Adding a "position" number in Mysql

Question

I got the following query and result currently:

mysql> SELECT songname,COUNT(*) as count FROM etrstats WHERE songname != '' AND `when` >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)  GROUP BY songname ORDER BY count DESC LIMIT 5;
+--------------------------------------+-------+
| songname                             | count |
+--------------------------------------+-------+
| Meghan Trainor - All About That Bass |    67 |
| The Cars - Drive                     |    55 |
| Train - Drops Of Jupiter             |    54 |
| C.W. McCall - Convoy                 |    52 |
| The Script - Superheroes             |    48 |
+--------------------------------------+-------+

What I want to do is add a second, temporary field to the query that shows which position it is after the sort. So the result would look like this:

+-----+--------------------------------------+-------+
| Pos | songname                             | count |
+-----+--------------------------------------+-------+
| 1   | Meghan Trainor - All About That Bass |    67 |
| 2   | The Cars - Drive                     |    55 |
| 3   | Train - Drops Of Jupiter             |    54 |
| 4   | C.W. McCall - Convoy                 |    52 |
| 5   | The Script - Superheroes             |    48 |
+-----+--------------------------------------+-------+

Answer 1

SET @x = 0;
SELECT (@x:=@x+1) Pos,* FROM
(
    SELECT songname,COUNT(*) as count FROM etrstats
    WHERE songname != '' AND `when` >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
    GROUP BY songname ORDER BY count DESC LIMIT 5
) A;

Give it a Try !!!

I do incrementing of temp variables in other posts

• Jun 08, 2012 : Update rank on a large table
• Jun 03, 2013 : Embedded select with rank