Post reply

Dynamic Order By

  • April 29, 2008 at 3:33 am

    #184927

    Dear All,

    I have a set of rows, a sample listed below

    IDStatus PageID

    110

    2112

    3215

    440

    500

    6413

    700

    8415

    920

    Now I wish to list the rows in Status ASC order, however, only those rows that have the Page ID > 0 will be ordered.

    How can i achieve that?

    I tried something like

    ORDER BY CASE pageID > 0 THEN Status ASC

    However I have a syntax error

    Thanks for your help and time

    Johann

  • April 29, 2008 at 3:42 am

    #808203

    What will happen to the rows that aren't ordered? Please show us how your result set should look.

    John

  • April 29, 2008 at 3:51 am

    #808205

    The rows that are not ordered, will be listed after the ordered rows

    So the resultset should look something like this:-

    ID Status PageID

    2 1 12

    3 2 15

    6 4 13

    8 4 15

    4 4 0

    5 0 0

    7 0 0

    9 2 0

    1 1 0

  • April 29, 2008 at 3:52 am

    #808206

    try the following -

    select Id, Status, PageId from YourTable

    order by case when PageId > 0 then Status End

    http://rajanjohn.blogspot.com

  • April 29, 2008 at 3:58 am

    #808209

    I clarified with my project leader

    this is enough

    ORDER BY PageID DESC, Status ASC

  • April 29, 2008 at 4:00 am

    #808210

    thanks for your help guys

  • April 29, 2008 at 4:10 am

    #808216

    but there you cant change the "> 0" to any other value, say "> 5". Or, is the PageId value not an issue?

  • April 29, 2008 at 4:18 am

    #808217

    Seems like the important thing is that the PageID > 0, that is why I am ordering in

    ORDER BY PageID DESC, Status ASC

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

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