Sorting Union Query (1 Viewer)

Jonny

Registered User.
Local time
Today, 22:58
Joined
Aug 12, 2005
Messages
144
Having a Union Query that consist of queries :
Code:
SELECT * FROM Degree5Alert
UNION
SELECT * FROM Degree6Alert
UNION
SELECT * FROM Degree6PAlert
UNION
SELECT * FROM Degree11Alert
UNION
SELECT * FROM Degree12Alert
UNION
SELECT * FROM Degree13Alert
UNION
SELECT * FROM Degree14Alert
UNION 
SELECT * FROM Degree15Alert;
Column "Degree" is common for all queries , however query result is not sorted.
How to sort it?

Thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Jan 23, 2006
Messages
15,364
Where and why do you think the result should be in some sort order?

To get an ordered set, try an Order By.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:58
Joined
May 7, 2009
Messages
19,169
SELECT 1 As Expr1, * FROM Degree5Alert
UNION
SELECT 2, * FROM Degree6Alert
UNION
SELECT 3, * FROM Degree6PAlert
UNION
SELECT 4, * FROM Degree11Alert
UNION
SELECT 5, * FROM Degree12Alert
UNION
SELECT 6, * FROM Degree13Alert
UNION
SELECT 7, * FROM Degree14Alert
UNION
SELECT 8, * FROM Degree15Alert;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:58
Joined
Oct 29, 2018
Messages
21,358
Hi. I usually put the ORDER BY on the very last query in the union.
 

Jonny

Registered User.
Local time
Today, 22:58
Joined
Aug 12, 2005
Messages
144
Each query separately gives workers and their degrees. For instance Degree5Alert will show all workers with a degre 4, e.t.c.
However somehow union query shows a mix of degrees..
 

plog

Banishment Pending
Local time
Today, 15:58
Joined
May 11, 2011
Messages
11,611
Column "Degree" is common for all queries

That should go without saying. You can only UNION common fields. If one of those queries/tables has an extra column, or is missing a column present in all others, it will not work.

What type are all those Degree... objects? Are they queries themselves, or are they tables?
 

plog

Banishment Pending
Local time
Today, 15:58
Joined
May 11, 2011
Messages
11,611
Just missed your reply stating those are all queries---That is not the way to do this.

You should be able to build a one query that gives you the data you want, without a UNION. Can you post 2 sets of data?

A. Starting data from your table(s). Show table and field names and include enough data to cover all cases.

B. Expected results of A. Show me the data you expect to end up with when you feed in the data from A.

Again, 2 sets of data--starting and ending.
 

Jonny

Registered User.
Local time
Today, 22:58
Joined
Aug 12, 2005
Messages
144
Thank you, dear friend, that did the job! How I only hide a column "Expr1"?
SELECT 1 As Expr1, * FROM Degree5Alert
UNION
SELECT 2, * FROM Degree6Alert
UNION
SELECT 3, * FROM Degree6PAlert
UNION
SELECT 4, * FROM Degree11Alert
UNION
SELECT 5, * FROM Degree12Alert
UNION
SELECT 6, * FROM Degree13Alert
UNION
SELECT 7, * FROM Degree14Alert
UNION
SELECT 8, * FROM Degree15Alert;
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:58
Joined
May 7, 2009
Messages
19,169
make a Datasheet Form and hide it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:58
Joined
May 7, 2009
Messages
19,169
oh, if you can make another Query from the union, then you can hide the column from the new query.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:58
Joined
Feb 28, 2001
Messages
27,001
I'm with plog on this. If those are all queries of the same table, then your UNION query merely needs to be a single query that has appropriate ordering. This is like the old saying "making three left turns just to avoid going right."

You are breaking apart your table with the multiple queries - but then rejoining what you split out, and in the process seeking to re-order it. Take what you had originally and just re-order that, avoiding the multiple extra steps. Not to mention that if this is a large enough table, it will be faster because single queries usually run a lot faster than multiple layered queries.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:58
Joined
May 7, 2009
Messages
19,169
if all queries on same table:

select * from table1 where degree between 5 and 15 order by degree asc;
 

Guus2005

AWF VIP
Local time
Today, 21:58
Joined
Jun 26, 2007
Messages
2,645
or
Code:
select * from table1 where degree in ('5A','6A','6PA','11A','12A','13A','14A','15A') order by degree asc;
HTH:D
 

Users who are viewing this thread

Top Bottom