Sql Server Query With Union And Different Order By To Each Section?

I've searched here on the site. and there are many version of answers to this question. But couldn't find what I was looking for for this specific question : lets say that xxx,yyy

Solution 1:

You have to apply a single order by clause to the entire union, or else the ordering isn't well defined:

SELECT a,1as Pos,a as Ord from xxx
UNIONALLSELECT f,2,-f from yyy
UNIONALLSELECT t,3,t from zzz
ORDERBY Pos,Ord

However, the -f might feel like a dirty trick to achieve the opposite ordering (or may not be entirely what you want if NULLs are included), so you could also do:

SELECT a,1as Pos,a as OrdAsc,0as OrdDesc from xxx
UNIONALLSELECT f,2,0,f from yyy
UNIONALLSELECT t,3,t,0from zzz
ORDERBY Pos asc,Ord asc,OrdDesc desc

---

I'm unclear on why you don't think it answers your question - perhaps because of the additional columns in the result set? If so, you can arrange for the whole UNION to be in a subquery:

createtable #xxx (a intnotnull)
createtable #yyy (f intnotnull)
createtable #zzz (t intnotnull)
insertinto #xxx (a) select1unionallselect2unionallselect3insertinto #yyy (f) select1unionallselect2unionallselect3insertinto #zzz (t) select1unionallselect2unionallselect3SELECT a FROM (
SELECT a,1as Pos,a as Ord from #xxx
UNIONALLSELECT f,2,-f from #yyy
UNIONALLSELECT t,3,t from #zzz
) t
ORDERBY Pos,Ord

results:

a
----
1
2
3
3
2
1
1
2
3

Solution 2:

Things to keep in mind while UNIONing:

• UNION allows you to create a RESULTSETwith same type data from different SELECT statement.
• The RESULTSET is generated with the column name of leading(first) SELECT statement.
• For other SELECT statement columns data type should be matched according to the leading(first) SELECT statement.
• For ORDERing, its applied on the RESULTSET, consequently in case of UNION, it only allows ORDER BY at the last SELECT statement but takes column name according to leading(first) SELECT statement, so below example is true:
• Finally, since in the RESULTSET for UNIONing with different tables/sources aggregates all data into a column(according to first(leading) SELECT statement), so you can not apply ASC and DESC for same column.

Right

SELECT a FROM xxx 
UNIONSELECT f FROM yyy
UNIONSELECT t FROM zzz orderby a asc

Wrong: according to you

SELECT a FROM xxx 
UNIONSELECT f FROM yyy
UNIONSELECT t FROM zzz orderby a asc, a desc