June 24, 2009 at 4:50 am
Hi,
I need to select the above rows in ascending order but want the row having t='O' as last row.
(Something like conditional sorting).
[Code]
declare @t table (t varchar(5))
insert into @t values('F')
insert into @t values('A')
insert into @t values('O')
insert into @t values('L')
insert into @t values('D')
insert into @t values('Z')
insert into @t values('S')
select * from @t
[/Code]
Expected Output
t
---
A
D
F
L
S
Z
O
June 24, 2009 at 6:03 am
Try adding a computed column in a subquery and then order by that column:
declare @t table (t varchar(5))
insert into @t values('F')
insert into @t values('A')
insert into @t values('O')
insert into @t values('L')
insert into @t values('D')
insert into @t values('Z')
insert into @t values('S')
select * from (
select *, sortcolumn = case t when 'O' then 999 else row_number() over (order by t) end
from @t
) as subqry
order by sortcolumn
Hope this helps
Gianluca
-- Gianluca Sartori
June 24, 2009 at 6:04 am
or:
SELECT t
FROM @t
ORDER BY CASE t WHEN 'O' THEN 1 ELSE 0 END, t
June 24, 2009 at 6:10 am
Ken McKelvey (6/24/2009)
or:
SELECT t
FROM @t
ORDER BY CASE t WHEN 'O' THEN 1 ELSE 0 END, t
Much better than mine.
-- Gianluca Sartori
June 24, 2009 at 6:14 am
:w00t: Thank you guys this is what i needed. :w00t:
But i didnt got how case works in the above order by clause ?
June 24, 2009 at 6:18 am
The case expression evaluates row by row the value of the column t and then gets into the appropriate "then". When the row containing "O" is evaluated, the expression returns 0, in all the other cases it returns 1.
The second sort column is t itself, so it order by first: the case expression, second: the t column.
Regards
Gianluca
-- Gianluca Sartori
June 24, 2009 at 6:26 am
Is it always needed to write 0 and 1 in the case.
What does 0 and 1 refer here?
does select becomes " select t from @t order by 1,t" at times ?
June 24, 2009 at 6:30 am
You always need to order by the case expression first. Ordering by a constant integer value means ordering by the Nth column of the results. In your example, order by 1 means "order by t".
-- Gianluca Sartori
June 24, 2009 at 6:41 am
Sorry, but if i use any random integer like 89 then this also works ?
June 24, 2009 at 6:47 am
You can use in the case expression any numbers you like, they just define the order you want to assign to the row in one case (my condition is met --> assign a high value, so that it sorted as last) and in the other cases (my condition is not met --> assign a low value, so that it is sorted as first).
If you don't use a case expression, but something like ORDER BY 3, then you will order by the 3rd column in your result set.
I'm sorry if I was not clear enough, English is not my native language as you can see from my name.
-- Gianluca Sartori
June 24, 2009 at 6:57 am
Thanks again ... i am a newbie so few doubts are still there !
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply