order by

Question

Post reply

order by

reddy06

SSC-Addicted

Points: 403
More actions
April 5, 2010 at 9:34 am

#217384

I have a table with one column.
My column has values 1,2,3,4,5
I need to order the table by 2,3,1,5,4. What is the query?
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

something like this? you can use a case statement in the ORDER BY for custom results.

ORDER BY

CASE

WHEN YOURCOLUMN = 2 THEN 10

WHEN YOURCOLUMN = 3 THEN 20

WHEN YOURCOLUMN = 1 THEN 30

WHEN YOURCOLUMN = 5 THEN 40

WHEN YOURCOLUMN = 4 THEN 50

ELSE 99

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

reddy06 SSC-Addicted Points: 403 More actions · Answer 2

Hi i am using distict in the select statement and i am getting the following error if i use case statement

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

and i have specified the column name

Thanks in advance

Lowell SSC Guru Points: 323518 More actions · Answer 3

you'd have to show us the sql statement...it's a simple syntax error, but we'd need to see the statement to help.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

reddy06 SSC-Addicted Points: 403 More actions · Answer 4

select tid,t.Num from dbo.Table1 t

order by t.tId, CASE

WHEN t.Num = 1 THEN 10

WHEN t.Num = 2 THEN 20

WHEN t.Num = 3 THEN 30

WHEN t.Num = 4 THEN 14

WHEN t.Num = 5 THEN 50

ELSE 99

END

It is working fine

select distinct tid,t.Num from dbo.Table1 t

order by t.tId,CASE

WHEN t.Num = 1 THEN 10

WHEN t.Num = 2 THEN 20

WHEN t.Num = 3 THEN 30

WHEN t.Num = 4 THEN 14

WHEN t.Num = 5 THEN 50

ELSE 99

END

Getting error

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 5

SELECT t.tid, t.Num

FROM

(SELECT DISTINCT Table1.tid, Table1.Num FROM dbo.Table1) As t

ORDER BY t.tId,

CASE

WHEN t.Num = 1 THEN 10

WHEN t.Num = 2 THEN 20

WHEN t.Num = 3 THEN 30

WHEN t.Num = 4 THEN 14

WHEN t.Num = 5 THEN 50

ELSE 99

END

reddy06 SSC-Addicted Points: 403 More actions · Answer 6

Thanks for your help

I have solved the problem.

I have assigned your case values to new column and i have sorted using the new column.

Thanks