Sorting Union Query

[Jonny]

Having a Union Query that consist of queries :

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]

Where and why do you think the result should be in some sort order?

To get an ordered set, try an Order By.

 

[arnelgp]

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]

Hi. I usually put the ORDER BY on the very last query in the union.

 

[Jonny]

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]

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]

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]

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]

make a Datasheet Form and hide it.

 

[Jonny]

I was thinking in SQL direction, too easy to be true, great!

make a Datasheet Form and hide it.

 

[arnelgp]

oh, if you can make another Query from the union, then you can hide the column from the new query.

 

[The_Doc_Man]

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]

if all queries on same table:

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

 

[Guus2005]

or

select * from table1 where degree in ('5A','6A','6PA','11A','12A','13A','14A','15A') order by degree asc;

HTH