simple order and limit question

[vince_w]

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?

[Dr John]

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.

[r937]

resolve the problem with ties as follows:

SELECT title
     , popularity
  FROM tags AS t
 WHERE ( SELECT COUNT(*)
           FROM tags
          WHERE popularity > t.popularity )
       < 5
ORDER
    BY title

[vince_w]

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.

[guelphdad]

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