UNION question
-
Hi all,
Simple UNION question.
I am UNION-ing multiple counts of different sets of records. Result of each union is one row. Each row's first column is a text constant of my choice(name of the result of the count). The result is ordered alphabetically based on my text constant in the first column.
example:
select 'A', count(column) where column = value1
union
select 'B', count(column) where column = value2
results in :
A
The question: How do I change the order the UNION rows are displayed (B - row first)?
Thank you
peter
-
select 'A', count(*), 1 as Ordered from dbo.SysColumns
union ALL
select 'B', count(*), 0 as Ordered from dbo.SysColumns
Order by Ordered
-
Thanks,
what about without the Ordered column ?
-
select 'A' as Data, count(*) from dbo.SysColumns
union ALL
select 'B', count(*) from dbo.SysColumns
Order by Data desc
is this what you want?
* Noel -
I also thaught about this solution but I presumed that there was more unions in the select...
-
You could always do something like this :
select 'A' as Data, count(*) from dbo.SysColumns
union ALL
select 'B', count(*) from dbo.SysColumns
Order by Case When Data = 'B' THEN 0 ELSE 1 END
-
Actually, I have 4 unions there.
I was just thinking of some general solution how to order n unions by any column without displaying any 'help' columns.
-
Select ...
union all
select ...
order by ColName
This is the trick, but in your exemple you want 'b' to come before 'a' which requires either a desc order or some sort of gymnastic in you want the order to be something like this :
'b'
'a'
'd'
'f'
-
Peter,
Remi just Put it clearly : Can you post your query and some data? I think that if what Remi or myself posted here is not what you want we are missing information that you have not provided
On the other hand the "show helper column" is a client side decision and you can choose not to do it there. If you still insist on doing it at the server you can always wrapp the query selecting the columns you need
Select ColA, ColB, .... ColN --no helper
from
(
Select ColA, ColB, .... ColN, Helper
union all
Select ColA, ColB, .... ColN, Helper
...
) Subq
Order by helper
* Noel -
Just like a wrestle tag team match... The ring is yours Noeld
. -
Actually I was Throwing the towel
* Noel -
thank you guys for some cool ideas, the query is way too long to post it here and i'm lazy to write a shorter example, but the select from select will do. I just have to write some function to generate the order i want and than dump the helper column. Sorry for the crapy explanation.
p.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply