1

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 |
+-----+--------------------------------------+-------+

1 Answer 1

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

1
  • Thank you, this was what I was looking for, though had to make a slight change to it(could perhaps be that I'm on MySQL 5.5?) MySQL didn't like the Pos,* FROM, had to change that to Pos,songname,count FROM.
    – Thor Erik
    Nov 11, 2014 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.