Solved Order by/Sort issue with UNION query (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:19
Joined
Apr 27, 2015
Messages
6,286
Good afternoon everyone,
I have three select queries (from the same table) that I need to use as a RowSource for a combo box.

When run individually, the Order By works as designed. However, if appended to a temp table or in a Union query, the order gets garbled. I was able to make a Filter field with the Union, but the source query Order By gets over-rode.

In summary, I need to run the queries (step 1, step 2 and set 3) in sequence and have the original sort/order by maintained. Any ideas?
 

Attachments

  • WMT_User_PR_V2.0_DRAFT.zip
    973.1 KB · Views: 239
Last edited:

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:19
Joined
Apr 27, 2015
Messages
6,286
Hi there Paul...my truncation was caused by user-error! It should be edited properly now...
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:19
Joined
Jan 23, 2006
Messages
15,364
John,

Were you still writing???
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:19
Joined
Sep 21, 2011
Messages
14,048
John,
What is the question?, as the fields are not in the same order.?
2 & 3 have Folder, TagDate & Tag
1 has TagDate,Tag then Folder ?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:19
Joined
Apr 27, 2015
Messages
6,286
John,

Were you still writing???
I was...the DB is only accessible through a Citrix Server session and I tried to upload it there and post...the lag was crazy so I saved it there and finished it on my PC.
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:19
Joined
Jan 23, 2006
Messages
15,364
Does this do what you need?


Code:
select * from
(SELECT *, 1 as filter FROM [q_TAG_folders_Step_1]
UNION SELECT *, 2 as filter FROM [q_TAG_folders_Step_2]
UNION SELECT *, 3 as filter FROM [q_TAG_folders_Step_3])
ORDER BY FILTER ;
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:19
Joined
Apr 27, 2015
Messages
6,286
John,
What is the question?, as the fields are not in the same order.?
2 & 3 have Folder, TagDate & Tag
1 has TagDate,Tag then Folder ?
The first query selects record where the Tag Date is Not Null and sorted by Folder Number - this set must be first
The second query selects records Where Tag Date is Null and the Tag Notes are Not Null, sorted by Folder Number - these must be second
The third selects records where both Tag Date and Tag Notes are both Null (ZLS) and sorted by Folder Number - These are last

Hope that clears it up...
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:19
Joined
Sep 21, 2011
Messages
14,048
That is what I had?
Code:
SELECT *, 1 as filter FROM [q_TAG_folders_Step_1]
UNION SELECT *, 2 as filter FROM [q_TAG_folders_Step_2]
UNION SELECT *, 3 as filter FROM [q_TAG_folders_Step_3]
ORDER BY FILTER;
but then noticed the fields were not in the same order?
I *thought* they had to be the same order and type for Unions?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:19
Joined
Apr 27, 2015
Messages
6,286
Does this do what you need?


Code:
select * from
(SELECT *, 1 as filter FROM [q_TAG_folders_Step_1]
UNION SELECT *, 2 as filter FROM [q_TAG_folders_Step_2]
UNION SELECT *, 3 as filter FROM [q_TAG_folders_Step_3])
ORDER BY FILTER ;
Close, by the initial sort/order by gets skewed. For example, the three sets get assigned the proper filter number but the Tag Date and Folder Sort are not sorted correctly
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:19
Joined
Sep 21, 2011
Messages
14,048
Close, by the initial sort/order by gets skewed. For example, the three sets get assigned the proper filter number but the Tag Date and Folder Sort are not sorted correctly
Would that be because the fields are not in the same order?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:19
Joined
Apr 27, 2015
Messages
6,286
There were originally in the same order, I moved them around a bit to see if that changed anything....no dice.

Im thinking I am may need a Sub that goes RBAR and numbers the records - I REALLY do not want to do that...
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:19
Joined
Sep 21, 2011
Messages
14,048
1 is sorted by TagDate, then Folder? 2 & 3 only by Folder?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:19
Joined
Apr 27, 2015
Messages
6,286
Code:
select * from
(SELECT *, 1 as filter FROM [q_TAG_folders_Step_1]
UNION SELECT *, 2 as filter FROM [q_TAG_folders_Step_2]
UNION SELECT *, 3 as filter FROM [q_TAG_folders_Step_3])
ORDER BY FILTER, [Tag Date] ;

Doesn't work either...
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:19
Joined
Sep 21, 2011
Messages
14,048
I would get the sort order working correctly first in the source queries.?
Why is 1 different?
Why a sort on each? Why not just on the Union?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:19
Joined
Apr 27, 2015
Messages
6,286
I would get the sort order working correctly first in the source queries.?
Why is 1 different?
Why a sort on each? Why not just on the Union?
The sort order in the source queries are perfect - it is when you union them that they get screwed up
 

isladogs

MVP / VIP
Local time
Today, 23:19
Joined
Jan 14, 2017
Messages
18,186
Hi John
I haven't looked at your app so this may or may not be relevant...
You can only have one sort in a union query applied after the final SELECT. If you apply more than one sort, the rest are ignored.
That one ORDER BY clause applies to each record returned by the union query. As the field names are governed by the first SELECT statement, it makes sense to ORDER BY that
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:19
Joined
Apr 27, 2015
Messages
6,286
Well I'm an idiot....

Paul, you were spot on. Once I changed the fields to line up with each other in the source queries, everything worked....all I had to do was add additional Order By's after the Union (Order By Filter, Tag Date, Folder) and all was well...
 
Last edited:

Users who are viewing this thread

Top Bottom