UNION question

  • Thread starter Thread starter terry w
  • Start date Start date
T

terry w

lets say there is a union query like this;

SELECT tblP.* FROM tblP WHERE ...etc.
UNION
SELECT tblQ.* FROM tblQ WHERE ...etc.
UNION
SELECT tblR.* ... etc.

Now, I want to sort the resulting table by the field common field [PO-type].
Is it proper to use ORDER BY [PO-type] ,without actually mentioning a
table name. I don't know how to use something like ORDER BY
tbl???.[PO-type].

Terry W.
 
Correct.

The ORDER BY clause goes after the last SELECT only, and the field names are
unique in a UNION. (Alias if necessary.)
 
lets say there is a union query like this;

SELECT tblP.* FROM tblP WHERE ...etc.
UNION
SELECT tblQ.* FROM tblQ WHERE ...etc.
UNION
SELECT tblR.* ... etc.

Now, I want to sort the resulting table by the field common field [PO-type].
Is it proper to use ORDER BY [PO-type] ,without actually mentioning a
table name. I don't know how to use something like ORDER BY
tbl???.[PO-type].

Terry W.
Click to expand...

You only get one sort... and it needs to be in the *last* SELECT statement.
I'd really avoid using SELECT * in UNION queries; you may be able to get away
with it, but I'd worry that the different tables might have different number
or different order of fields.

Try putting

ORDER BY [PO-type];

at the end of the last SELECT in the UNION; if the fieldname varies from table
to table, you might need to use

ORDER BY 4;

if the PO-Type is the fourth field in the query.
 
Back
Top