Need a sorting query.

Question

Post reply

Need a sorting query.

descentflower

Ten Centuries

Points: 1227
More actions
June 24, 2009 at 4:50 am

#133439

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

Ken McKelvey SSCoach Points: 18842 More actions · Answer 2

or:

SELECT t

FROM @t

ORDER BY CASE t WHEN 'O' THEN 1 ELSE 0 END, t

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

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

descentflower Ten Centuries Points: 1227 More actions · Answer 4

:w00t: Thank you guys this is what i needed. :w00t:

But i didnt got how case works in the above order by clause ?

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

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

descentflower Ten Centuries Points: 1227 More actions · Answer 6

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 ?

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

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

descentflower Ten Centuries Points: 1227 More actions · Answer 8

Sorry, but if i use any random integer like 89 then this also works ?

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

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

descentflower Ten Centuries Points: 1227 More actions · Answer 10

Thanks again ... i am a newbie so few doubts are still there !