Solved Order by/Sort issue with UNION query

[NauticalGent]

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?

 

[Gasman]

OK, yours is the first post that I have seen tonight that appears truncated.?

This is the second? https://www.access-programmers.co.u...pen-pg2-of-tab-control-to-same-record.316336/

 

[NauticalGent]

Hi there Paul...my truncation was caused by user-error! It should be edited properly now...

 

[jdraw]

John,

Were you still writing???

 

[Gasman]

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]

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]

Does this do what you need?

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]

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]

That is what I had?

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]

Does this do what you need?

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]

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]

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]

1 is sorted by TagDate, then Folder? 2 & 3 only by Folder?

 

[NauticalGent]

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]

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]

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

 

[Gasman]

Out of ideas then, sorry.

 

[NauticalGent]

Out of ideas then, sorry.

Me too, thanks for trying. Ill start working on that #$^% Sub until (hopefully) someone else has a solution.

 

[isladogs]

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]

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...