Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: Conditionnal DESC/ASC

    Hello,

    I'd like to condition the sort order of a query.
    Let's give an example:
    Code:
    SET condition = TRUE;
    SELECT id, name
    FROM table_1
    ORDER BY name IF(condition, DESC, ASC)
    For sure this doesn't work.
    Could someone tell me how can I do that ?
    Is there an EVAL function which allows to do that ?
    Like
    Code:
    SELECT id, name
    FROM table_1
    ORDER BY name EVAL(IF(condition, "DESC", "ASC"))
    (I'm in a procedure)

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    We don't order by expressions, only derived columns (by name or index).

    The solution you're looking for is something like this
    Code:
    SELECT id
         , name
         , CASE WHEN condition = 'ASC'  THEN name END As [sort_asc]
         , CASE WHEN condition = 'DESC' THEN name END As [sort_desc]
    FROM   your_table
    ORDER
        BY sort_asc  ASC
         , sort_desc DESC
    
    SELECT id
         , name
    FROM   (
            SELECT id
                 , name
                 , CASE WHEN condition = 'ASC'  THEN name END As [sort_asc]
                 , CASE WHEN condition = 'DESC' THEN name END As [sort_desc]
            FROM   your_table
           ) x
    ORDER
        BY sort_asc  ASC
         , sort_desc DESC
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    We don't order by expressions
    You mean you don't use to do that or it's not doable ?
    Because actually this
    Code:
    SELECT id, name FROM table ORDER BY IF(TRUE, id, name)
    works

  4. #4
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Finally I'm going to make a view,
    so it will be more maintainable.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    As you've noticed, it is possible to order by an expression. It's also possible for me to take a bath with the toaster, doesn't mean I want to do it!

    Essentially the SQL standards state that you should order order by derived values, by their name/alias or their index (first column = 1, second column = 2, etc) - which is why we don't do it.

    ...and just to sound like (more of) a bore - you can't create views with an ORDER BY clause!


    You can easily create the view without that bit, and then when you select from the view add the order by clause; e.g.
    Code:
    SELECT id
         , name
    FROM   your_view
    ORDER
        BY sort_asc  ASC
         , sort_desc DESC
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Yes, that's what I wanted to do.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by georgev
    We don't order by expressions, only derived columns (by name or index).
    Why is that? Is it a performance thing? Ordering of data is most likely to be done on data that is about to be displayed to the user. The amount of data to be displayed would normally be quite small (less than 100 records, preferably less than 20 records) otherwise the user will just ignore the report and use it instead as a paper weight. I see how you order the data to be more a business requirement rather than a performance requirement so if the business wanted the data to be displayed in order of field x that's exactly what they'll get - most likely they'll want the ability to order by just about every field that's on the display.

    Quote Originally Posted by gtk
    Finally I'm going to make a view,
    so it will be more maintainable.
    If you're doing this with a stored proc you could just do it this way which might be slightly more easier to read (ie more maintainable) :
    Code:
    SET condition = TRUE;
    
    if condition then
       SELECT id, name
       FROM table_1
       ORDER BY name
    else
       SELECT id, name
       FROM table_1
       ORDER BY name DESC
    end if;
    Other options to consider if you're outputting the data to some report via an external program such as PHP might be to just build the final select statement in PHP depending on your condition.
    Code:
    $condition = TRUE;
    
    if ( $condition ) {
       $sql = "SELECT id, name FROM table_1 ORDER BY name"
    } else {
       $sql = "SELECT id, name FROM table_1 ORDER BY name desc"
    }
    Of course if you were using a numeric value to order by then it would be easy enough:
    Code:
    SET condition = TRUE;
    
    SELECT id, name
    FROM table_1
    ORDER BY IF( condition , val , val * -1)
    Mike

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by mike_bike_kite
    Why is that?
    It's a standards thing
    Quote Originally Posted by georgev
    Essentially the SQL standards state that you should order order by derived values, by their name/alias or their index (first column = 1, second column = 2, etc) - which is why we don't do it.
    As for performance - if you think about how a query executes, the very last step is generally the order by, right? Which means an expression would have to be re-evaluated again.

    ...in theory - this is one I've not proved yet but I could do if anyone is interested enough (proof would be SQL Server )
    George
    Home | Blog

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by georgev
    It's a standards thing
    Who's standards? So you're saying that if the business wants to be able to order it's report by any field on screen then you would simply say no and tell them you can't because "it's a standards thing"???? You're braver than me!

    Quote Originally Posted by georgev
    As for performance - if you think about how a query executes, the very last step is generally the order by, right? Which means an expression would have to be re-evaluated again.
    So what? that's hardly a reason to refuse to do it. If the requirement is for a report to ordered by an expression then that's what you provide. If you can suggest a better way of doing things then great but if I'd asked for a certain ordering and you said you wouldn't provide it because it means an expression would have to be re-evaluated again then I'd be having a quite word with your boss.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ANSI / ISO standards (sorry for not being clearer earlier)
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    As for examples of how to remove the expression from the order by - see post 2 - it contains two variations of this theme.
    George
    Home | Blog

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by georgev
    ANSI / ISO standards ...
    As for examples of how to remove the expression from the order by - see post 2 - it contains two variations of this theme.
    I think we'll have to agree to differ on what's more maintainable then. To me example 2b looks like a typical NZDF solution to ordering and the more fields that are candidates for ordering then the more complex that query becomes. I'm guessing that in the least complex case then your example might be marginally faster but I doubt if you'd be able to measure the difference in real time. I certainly know which example I'd use if it was me that had to maintain the code.

    Perhaps it's just me seeing things differently to the rest of the civilised world ... again.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You appear to have overlooked the subtlety in the second example...

    The first query returns the derived columns as part of the resultset while the second example does not. Both examples are ordered correctly based on the a parameter setting.
    George
    Home | Blog

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by georgev
    You appear to have overlooked the subtlety in the second example...
    I think the fact it was too subtle for me indicates it might be a maintenance headache in future

    I know my example used a choice of two selects rather a single complex one but the example below is pretty quick to understand and would work in a sproc :
    Code:
    if condition then
       SELECT id, name
       FROM table_1
       ORDER BY name
    else
       SELECT id, name
       FROM table_1
       ORDER BY name DESC
    end if;

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I like that suggestion too Mike. It will be the best performer of the lot because it only has a single order by (and no computations either).
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •