SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY if the column is empty?

    The result of my table has some empty cells where there is no data in that particular column for the item.

    But where there is data it is numerical and I want to order it. The problem is ASC and DESC both place the items with empy fields at the top! I would rather they were always shoved to the bottom.

    Is there a way to do this?
    all code tested in:
    FireFox0.9, Opera7.51, Mozilla1.7, InternetExplorer5+6

  2. #2
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it not possible to perform the following:

    Code:
    ...your SQL code...
    WHERE column <> ""
    ORDER BY column ASC

  3. #3
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I cannot append that to the end of the query, it doesn't work.

    The query is quite complex and integrating that is not an option. I need something that I can just add to the ORDER BY section.
    all code tested in:
    FireFox0.9, Opera7.51, Mozilla1.7, InternetExplorer5+6

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ascending:
    Code:
    order 
        by case when columnx is null then 1 else 0 end
         , columnx asc
    descending:
    Code:
    order 
        by case when columnx is null then 1 else 0 end
         , columnx desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    That doesn't seem to work because columnx is an average, e.i. is Average_Num from "Avg(table.varNum) AS Average_Num".
    all code tested in:
    FireFox0.9, Opera7.51, Mozilla1.7, InternetExplorer5+6

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    can't help you if i can't see the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    I've sent it to you.
    all code tested in:
    FireFox0.9, Opera7.51, Mozilla1.7, InternetExplorer5+6

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT ...
         , Avg(ratings.rating) AS Average_Rating     
      FROM ...
    GROUP 
        BY ...
    ORDER 
        BY CASE WHEN Average_Rating IS NULL THEN 1 ELSE 0 END
         , Average_Rating ASC
    if this doesn't work, there must be some other reason

    i think
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the error I get:

    Error performing query: Unknown column 'Average_Rating' in 'order clause'

    The thing is if I just order it by Average_Rating DESC or ASC it works, accepting Average_Rating as a column. But as it can be empty this is not a good way to do it.

    I could make sure that there is always a rating but practically that would be very difficult, and I need the backup just in case. . .
    Last edited by Subjective Effec; Jul 24, 2004 at 06:19.
    all code tested in:
    FireFox0.9, Opera7.51, Mozilla1.7, InternetExplorer5+6

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    phenomenal

    you're right, i can't get it to work either

    and i've been all over the docs

    oh well, time for a hack
    Code:
    SELECT ...
         , Avg(ratings.rating) AS Average_Rating    
         , coalesce(Avg(ratings.rating)
                  / Avg(ratings.rating),-1) as sortkey
      FROM ...
    GROUP 
        BY ...
    ORDER 
        BY sortkey desc
         , Average_Rating ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    Well this has certainly been the runaround hasn't it?

    But it works now! And frankly, I'd expect no less from you Rudy, you always come up with the goods. Thanks yet again. I think I'm going to have to link your site from mine and put you in the credits! Somehow I think that isn't the first time I'm said that.

    Now I'm off to research COALESCE, I have to know how my own site works after all.

    Why do you call it a hack though?

    edit [post-research]

    Ahhh, I see. Genius. Dividing Average_Rating by itself will give 1 if it is not empty but NULL is if it is and then -1. You then sort by Sortkey (either 1 or -1) and then by the actual Average_Rating.

    What version of mySQL had COALESCE introduced? I can't see in the mySQL docs. At home I have 3.23.49, my host (where the site is destined to end up) uses 3.23.24. Well that's what they say on the site. phpinfo tells me they have 3.23.39. It's still a lower version than mine.
    all code tested in:
    FireFox0.9, Opera7.51, Mozilla1.7, InternetExplorer5+6

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks for the kind words

    the docs say that COALESCE came in with 3.23.3

    you could use IF(foo is null, -1, foo)

    i think
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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