ORDER BY in UNION 1

[FoxProProgrammer]

Hi!

I wrote a Query whose results might look like this:

Part Number Description
123 ABC 1
456 ABC 2
789 ABC 5
873 ABC 10
329 BCD 2
234 BCD 3
897 BCD 21

The Query is:

SELECT tbl_X.PN AS [Part Number], tbl_X.Description FROM tbl_X
ORDER BY IIf(IsNumeric(Mid([Description],2,1)),Left([Description],1),IIf(IsNumeric(Mid([Description],3,1)),Left([Description],2),Left([Description],3))), Val(IIf(IsNumeric(Mid([Description],2,1)),Mid([Description],2),IIf(IsNumeric(Mid([Description],3,1)),Mid([Description],3),Mid([Description],4))));

The Query works fine when used alone.

If ORDER BY is simply set to Description, the results would look like this:

Part Number Description
123 ABC 1
873 ABC 10
456 ABC 2
789 ABC 5
329 BCD 2
897 BCD 21
234 BCD 3

Now I want to include the query above in a UNION as follows:

SELECT tbl_X.PN AS [Part Number], tbl_X.Description FROM tbl_X
ORDER BY IIf(IsNumeric(Mid([Description],2,1)),Left([Description],1),IIf(IsNumeric(Mid([Description],3,1)),Left([Description],2),Left([Description],3))), Val(IIf(IsNumeric(Mid([Description],2,1)),Mid([Description],2),IIf(IsNumeric(Mid([Description],3,1)),Mid([Description],3),Mid([Description],4))))
UNION 
SELECT tbl_Y.PN AS [Part Number], tbl_Y.Description
FROM tbl_Y;

The results are sorting by Part Number like this:

Part Number Description
123 ABC 1
234 BCD 3
329 BCD 2
456 ABC 2
789 ABC 5
873 ABC 10
888 CAP 0.1UF
897 BCD 21

I want the results to be sorted by Description as follows:

Part Number Description
123 ABC 1
456 ABC 2
789 ABC 5
873 ABC 10
329 BCD 2
234 BCD 3
897 BCD 21
888 CAPACITOR 0.1UF

The ORDER BY in the first part of the UNION isn't working, and I don't understand why the results are sorted by Part Number. Any idea what I'm doing wrong?

Thanks very much.


dz

 

[maxhugen]

G'day dz

The Order By needs to be at the end, after the 2 selects being unioned:

SELECT
....
UNION
SELECT
....
ORDER BY
....

HTH

Max Hugen
Australia

 

[FoxProProgrammer]

G'dmorning, Max!

I thought your suggestion to be the case, and I even tried putting the ORDER BY after the second SELECT before posting here. But, Access returns an error...

The ORDER BY expression .... includes fields that are not selected by the query. Only those fields selected in the first query can be included in an ORDER BY expression.

Thank you,




dz

 

[PHV]

Two notes:
1) use UNION ALL
2) use the ordinal position for sorting: ORDER BY 2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[FoxProProgrammer]

Well, you did it again PHV! Did you write Access? lol

I can't find anything in the Help that describes what ORDER BY 2 does. In fact, the help in Access on ORDER BY says

[ORDER BY field1 [ASC | DESC ][, field2 [ASC | DESC ]][, ...]]]

field1, field2 The names of the fields on which to sort records.

I'll look around Microsoft's website to see if I can find anything on this.

Thanks!

dz

 

[maxhugen]

dz, you could try creating a query as the first part of your union, and adding a calculated field which returns your expression:

IIf(IsNumeric(Mid([Description],2,1)),Left([Description],1),IIf(IsNumeric(Mid([Description],3,1)),Left([Description],2),Left([Description],3))), Val(IIf(IsNumeric(Mid([Description],2,1)),Mid([Description],2),IIf(IsNumeric(Mid([Description],3,1)),Mid([Description],3),Mid([Description],4))))

If you named this field as 'OrderExp', your Union query might look like:

SELECT PN AS [Part Number], Description, OrderExp FROM myQuery
UNION
SELECT PN AS [Part Number], Description, Description
FROM tbl_Y
ORDER BY OrderExp ;

BTW, "ORDER BY 2" means to order by the second field of the query, but may not work for you, as you appear to need to manipulate the Description from tbl_X to match that in tbl_Y.

HTH

Max Hugen
Australia