SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Location
    Boston
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    simple order and limit question

    I have a simple table:

    tags(title, popularity)

    I'm trying to do a query that returns the 5 most popular tags but ordered (alphabetically) by title.

    select * from tags order by popularity DESC limit 5;

    returns the tags I want but I don't know how to order it alphabetically. Can someone help?

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    select * from (select * from tags order by popularity DESC limit 5) order by title;

    I think.

    that is a sub query in the brackets, which then passes its results, the top five by popularity, to the outer query, which takes everything it is given and sorts by title.

    if you'd just added order by popularity, title, it would sort within each popularity group
    eg cat, 7; ant, 7; zebra, 5; mouse, 4; horse, 4;
    would become ant, 7; cat, 7; zebra, 5; horse, 4; mouse, 4;

    But the query I gave should give you
    ant, 7; cat, 7; horse, 4; mouse, 4; zebra, 5;

    There's only one snag:
    suppose five animals have a popularity of 4, and the ones missing from the top five are ardvark, bat and chinchilla, your final query does not include them, but they should be there, in theory. Your query idea only works if there are exactly five at the top who all have a higher popularity than all the others. So it is a badly flawed idea.

  3. #3
    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)
    resolve the problem with ties as follows:
    Code:
    SELECT title
         , popularity
      FROM tags AS t
     WHERE ( SELECT COUNT(*)
               FROM tags
              WHERE popularity > t.popularity )
           < 5
    ORDER
        BY title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Location
    Boston
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks anyone know which one is more efficient? this is my first time readinng about ties so i'm not sure how it works and if it's better.

  5. #5
    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)
    if your data is 10, 9, 9, 8, 8, 8, 7

    the first solution does not give you what you want.

    the second solution gives you "top 5" including ties, so all values of 8, which are tied for fourth, would be included.

    thus speed is not a factor


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
  •