How to order by

Question

How to order by

dheer

Ten Centuries

Points: 1275
More actions
March 31, 2010 at 8:20 am

#220740

Hi i have situation where i need to order by not by desc or asc but according to given report
Ex if coolumn ALPA as B,C,D,E,R,T ,Z then i have to display according to report
Coloumn ALPA as z ,E,B,R,C,T

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

One way to acheive this would be to create a reference table with the distinct ALPA values and the desired ranking of each value, then to order your table you would simply need to join to the reference table and order by rank column

Lowell SSC Guru Points: 323518 More actions · Answer 2

maybe something like this?

Ex if coolumn ALPA as B,C,D,E,R,T ,Z then i have to display according to report

Coloumn ALPA as z ,E,B,R,C,T

ORDER BY

CASE

WHEN ALPA IN ('B','C','D','E','R','T' ,'Z')

THEN 2

ELSE 1

END DESC,ALPA DESC, OTHERCOLS

this would force items with ALPA in the selected group to appear first, followed by stuff that is outside of the desired group.

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!

Paul White SSC Guru Points: 150467 More actions · Answer 3

steveb. (3/31/2010)
One way to acheive this would be to create a reference table with the distinct ALPA values and the desired ranking of each value, then to order your table you would simply need to join to the reference table and order by rank column

This would be my preference, since it could take advantage of an index. ORDER BY CASE cannot.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

nguyennd SSCommitted Points: 1881 More actions · Answer 4

Try this SQL

order by

CASE WHEN ALPA = 'Z' THEN 0

WHEN ALPA = 'E' THEN 1

WHEN ALPA = 'B' THEN 2

WHEN ALPA = 'R' THEN 3

WHEN ALPA = 'C' THEN 4

WHEN ALPA = 'T' THEN 5

ELSE 99 END

😀

http://unitech.vn

xinyu.wang1 SSC Enthusiast Points: 154 More actions · Answer 5

yes, and it's easy to change the order when using this method. For order by case, the query need to be changed, so the application need to be changed.

Welcome to my website: DB Expert

Paul White SSC Guru Points: 150467 More actions · Answer 6

xinyu.wang1 (4/4/2010)
For order by case, the query need to be changed, so the application need to be changed.

Unless dynamic SQL is used 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi