How to order a UNION query that has a select all function
database, ms-access, sql
Solution
One thing that you can is add a field that you can use to sort the data:
Select "*" as AllRec, "<<All Records>>" as Allrecords, 1 as SortOrder
FROM Treatment
UNION
Select Treatment.TreatmentID, Treatment.Treatment, 2 as SortOrder
From Treatment
ORDER BY SortOrder, AllRec;
The `ORDER BY` is always applied last so it will not know the `Treatment.TreatmentID` column name.
Problem
So this query is for a combo box of a database, and I've tried to order the list of items that appears inside, however it wont work. This is the query ``` Select "*", "<<All Records>>" FROM Treatment UNION Select Treatment.TreatmentID, Treatment.Treatment From Treatment; ``` I tried adding Order By's for both parts of the query, and tried switching it around, still not working, any ideas? I tried to `ORDER BY Treatment.TreatmentID`, since that's the primary key for that table The ORDER BY works on the second part itself, when there was no union involved, by the way. Currently, the data is sorting as this : ``` <<All Records>> 1 Treatment Name 8 Treatment Name 9 Treatment Namw 10 Treatment Name 11 Treatment Name 12 Treatment Name 2 Treatment Name Etc... ``` The number is the ID, and the Treatment name (Obviously not all called Treatment Name) are from the field Treatment name. I need "All Records" up top, and then sort the data from the second part of the union, by the ID. Thanks in advance, Adam