SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by field and then count

    I have some rows that are ordered by a count DESC. That's fine, but I want to "pin" some specified fields to the top of the results. This field is empty for most results, but there are a few that have the date: first, second, third...

    eg.

    ORDER by FIELD (rating, 'first', 'second', 'third'), count DESC

    This doesn't work! How can I get the results to show the 'first', 'second', 'third' first and then order the rest by count DESC?

  2. #2
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have tried:

    ORDER by FIELD (rating, 'first', 'second', 'third') ASC, count DESC

    This pins them to the top but in reverse order eg. third,second,first, but then does correctly order the remainder by the count

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your error is because of your use of COUNT. COUNT is a function so you have to give it something to count. Also the ASC isn't necessary in the ORDER BY FIELD as you specify within which order you want.

    Code:
    ORDER BY
    FIELD (rating, 'first', 'second', 'third'), 
    count(*) DESC

  4. #4
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry my fault, it's actually a variable called review_count which I have created earlier in the full query!
    Weirdly, if I reverse the order of the fields, I get the order I need?! (ie third, second, first gives me the desired result!)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by skyline View Post
    This field is empty for most results, but there are a few that have the date: first, second, third...
    this is why it doesn't work, because when the rating isn't 'first' or 'second' or 'third' then the FIELD function returns 0, and of course 0 sorts ahead of 1, 2, and 3

    make sense now?

    workaround would be a CASE expression --
    Code:
    ORDER
        BY CASE WHEN rating = 'first' THEN 1
                WHEN rating = 'second' THEN 2
                WHEN rating = 'third' THEN 3
                ELSE 937 END
        , rating_count DESC
    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
  •