How to order a UNION query that has a select all function

Question

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

Taryn · Accepted Answer · 2013-01-29T17:22:21.993

6

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.

edited Jan 29 '13 at 17:22

answered Jan 29 '13 at 17:04

Taryn

242,637
56
362
405

Yeah, not working :( I dont need the two sorted together, i need SELECT ALL at the top, which it is, and i need the results from the second part of the query sorted amongst themselves, not with the select all – amartin94 Jan 29 '13 at 17:09
Nope, still not sorting :( Would you like a screenshot? – amartin94 Jan 29 '13 at 17:32
What is the data sorting as? – Taryn Jan 29 '13 at 17:33
Edit your original post and show some sample data and then the desired result – Taryn Jan 29 '13 at 17:34

score 1 · Answer 2 · answered Jan 29 '13 at 17:03

1

You can do this:

Select q.* from 
(Select "*", "<>" 
FROM Treatment UNION 
Select Treatment.TreatmentID, Treatment.Treatment 
From Treatment) q
ORDER BY q.whatever

answered Jan 29 '13 at 17:03

DNadel

495
1
5
13

score 0 · Answer 3 · answered Jun 02 '15 at 22:24

0

SELECT, "--- All Records ---", SortOrder = 0

UNION

SELECT TreatmentID, Treatment, SortOrder = 1 FROM Treatment

ORDER BY SortOrder, Treatment;

(Only 2 and half years late!)

answered Jun 02 '15 at 22:24

AJA

1

Opps - first select should be :SELECT 0 AS TreatmentId, "--- All Record ---" AS Treatment, SortOrder = 0 – AJA Jun 02 '15 at 22:28

How to order a UNION query that has a select all function

3 Answers3