SQL ORDER BY.. wTF

Discussion in 'OT Technology' started by noon, Aug 15, 2007.

  1. noon

    noon get high and teach me how to listen

    Joined:
    May 4, 2002
    Messages:
    3,384
    Likes Received:
    0
    Location:
    Lawrence, KS
    I have a column in one of my tables which I need to be naturally sorted, but it is an nvchar and also contains some rows which are not numbers, ie:


    1
    XL
    10
    10.5
    8
    L

    This should be ordered so that the result looks like:

    1
    8
    10
    10.5
    L
    XL

    Any clue on how to do this?
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Best to do the sort in the report that requires this information to be sorted. In access you COULD do this but it's kinda lame IMHO.

    SELECT Table1.size
    FROM Table1
    ORDER BY Format([size],"00.0");
     
  3. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    well, in MySQL a standard ORDER BY ... ASC clause will be sufficient. In the VarChar datatype numbers are sorted before letters. Unfortunatly, it sorts the data set: [ 1 a 2 3 4 b 5 6 7 8 9 10 ] into [1 10 2 3 4 5 6 7 8 9 a b ]. Other than that, it will do what you need.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well yeah... that's the problem :rofl:
     
  5. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    um, no, that is not the problem.... look at his sample data and it would satisfy what I" describe.
     
  6. noon

    noon get high and teach me how to listen

    Joined:
    May 4, 2002
    Messages:
    3,384
    Likes Received:
    0
    Location:
    Lawrence, KS
    The numbers need to be naturally sorted, meaning 1 2 3 10. Not 1 10 2 3. The problem is that the field is an nvchar, and sorting of an nvchar results in un-natural sorting. So, I was using ORDER BY CONVERT(FLOAT,size) successfully, but now I need to be able to have sizes such as M & XL.
     
  7. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    One path you might want to head down is to Union two result sets together. Something to the effect of:

    Code:
    (select * from whatever where size < 'A' order by convert(float, size) asc)
    union
    (select * from whatever where size >= 'A' order by size asc)
    
    The only problem is that most DBMSs will not preserve the order of the two result sets after unioning them. If you happen to be using Oracle though, a multiset union will preserve the order.

    Your best bet is probably to left pad the size somehow.
     
  8. noon

    noon get high and teach me how to listen

    Joined:
    May 4, 2002
    Messages:
    3,384
    Likes Received:
    0
    Location:
    Lawrence, KS
    I've tried left padding, but it puts the integer values in front of the decimal values, ie: 1 2 3 4 1.5 2.5
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Your solution doesn't sort as per his desired sort order, therefor, it's not a solution. You can't change the desired results and claim it's a solution :rofl:
     
  10. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Left pad with 0's - but you have to be careful not to do that with the non-numerics. Mine does work, but it's not exactly SQL-friendly :)
     
  11. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    You need to sort by the output of a procedure on that field. Something that knows to put numbers first, and characters last. You could convert the characters to digits and pad the front a lot to make that number really high. Do that within the procedure/routine.
     
  12. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    It doesn't account for the decimals though.
     
  13. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Yes, it does. Because if it is a number, you don't pad it. You can do shite like that in a proc.
     
  14. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You would have to add that as part of the functionality of procedure though - there's already a means to sort the values 0-9,a-z without creating a procedure.

    The more I think about it, the best solution is to mimic Access's format command using a mixture of cast and replace functions. Something like:

    CASE size WHEN isnumeric THEN ............ ELSE size END

    Fill in the ......... with something that converts any number into this format: 00.0 and convert it to a string.

    Then sort on that expression.

    Now. The problem you will THEN run into is if you add small and medium sizes. You probably want to sort it S,M,L,XL,XLL but it will actually sort it L,M,S,XL,XLL, in that case you're going to have to assign those sizes numeric numbers if you want to sort it properly.
     
  15. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    If its a character, covert to digits, and pad the digits with 1s to make it a large number, then order numerically. Leave numbers alone. Whats the problem? It does exactly what he wants. Problem solved. You could also assign specific values to sizes, if those are the only non-numeric values.
     
  16. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Okay, I'll bite. How do you convert a character to a digit?
     
  17. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    Let's just do post-processing of the result set in the application :)
     
  18. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Exactly. Although, if it were me, I'd just have a size/sort order table. When you get into things like shoe sizes where there's adult and child sizes, I don't see how you can organize it any other way than manually setting the sort order. It's like trying to come up with a nice formula for keeping track of tennis scoring
     
  19. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    This will vary between SQLs. :) But it sounds like he's only dealing with sizes? In which case you can convert with a CASE.
     
  20. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    For fucks sakes - what the fuck are you talking about? Val('L') = 0, Val('M') = 0. How the FUCK does that help!?

    I mean, maybe I'm COMPLETELY missing your point, but you are being so vague to the point that I think you're talking out of your ass.
     
  21. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    You are missing the point. Make 'XL' a large number. Larger than any of the numbers you will actually encounter. Make L one less than XL. And so on and so forth. Why is this so hard to understand?

    If you literally convert to numbers for the ordering, again, prepend lots of 9s.

    I'm not writing the SQL for you. I'm busy.
     
  22. noon

    noon get high and teach me how to listen

    Joined:
    May 4, 2002
    Messages:
    3,384
    Likes Received:
    0
    Location:
    Lawrence, KS
    This is in SQL Server 2005, if that is helpful
     
  23. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Make a sort order table, link the two - I think it's your best bet. Peyomp's solution does work, but I wouldn't consider it since it would require a hard coding (ie a procedure) to force the sorting. As your character sizes (S,M,L,XL) also need to be sorted "unnaturally", there is no magical function that will do what you want. You're going to have to tell the engine which size comes first - and the best way to do that is a separate table.
     

Share This Page