Post reply

How to order by

  • 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

  • March 31, 2010 at 8:22 am

    #1143124

    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

  • March 31, 2010 at 8:26 am

    #1143129

    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!

  • March 31, 2010 at 10:12 am

    #1143240

    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

  • April 1, 2010 at 4:21 am

    #1143800

    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

  • April 4, 2010 at 4:57 am

    #1145440

    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

  • April 4, 2010 at 7:30 am

    #1145458

    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

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

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