ranking entries in mysql table

Question

I have a MySQL table with many rows. The table has a popularity column. If I sort by popularity, I can get the rank of each item. Is it possible to retrieve the rank of a particular item without sorting the entire table? I don't think so. Is that correct?

An alternative would be to create a new column for storing rank, sort the entire table, and then loop through all the rows and update the rank. That is extremely inefficient. Is there perhaps a way to do this in a single query?

Answer 1

There is no way to calculate the order (what you call rank) of something without first sorting the table or storing the rank.

If your table is properly indexed however (index on popularity) it is trivial for the database to sort this so you can get your rank. I'd suggest something like the following:

Select all, including rank

SET @rank := 0;
SELECT t.*, @rank := @rank + 1
FROM table t
ORDER BY t.popularity;

To fetch an item with a specific "id" then you can simply use a subquery as follows:

Select one, including rank

SET @rank := 0;
SELECT * FROM (
  SELECT t.*, @rank := @rank + 1
  FROM table t
  ORDER BY t.popularity
) t2
WHERE t2.id = 1;