Get the rank of a specific row, by month of the year

Question

How can I make a query to get the rank of a row, in relation to the number of votes the row has compared to other rows during the same month of the year?

+----+---------+--------+---------------------+
| id | post_id | votes  |        date         |
+----+---------+--------+---------------------+
|  1 |       1 |  10342 | 2015-04-29 04:40:51 |
|  2 |       2 |   9432 | 2015-04-17 22:37:53 |
| 3  |      3  | 33064  | 2015-05-17 22:37:17 |
| 4  |      4  | 32741  | 2015-05-17 22:37:25 |
| 5  |      5  | 30362  | 2015-05-17 22:37:31 |
| 6  |      6  | 15321  | 2015-06-28 00:33:20 |
| 7  |      1  |  4356  | 2015-06-28 00:19:30 |
+----+---------+--------+---------------------+

For example, if I wanted to find the rank of post_id #1 for each month, it should return something like:

+----+------+---------+-------+---------------------+
| id | rank | post_id | votes |        date         |
+----+------+---------+-------+---------------------+
|  1 |    1 |       1 | 10342 | 2015-04-29 04:40:51 |
| 7  |    2 |      1  | 4356  | 2015-06-28 00:19:30 |
+----+------+---------+-------+---------------------+

This is because post_id #1 had the most votes during the 4th month of the year, but the second highest number of votes in the 6th month of the year.

How can I make this query?

You can use row number for this. See here: http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select — Ganesh Jadhav, May 06 '15 at 16:07

score 0 · Accepted Answer · answered May 06 '15 at 16:09

SELECT YEAR(t1.date) AS year, MONTH(t1.date) AS month, SUM(IF(t1.votes >= t2.votes, 1, 0)) AS rank
FROM YourTable AS t1
JOIN YourTable AS t2 ON YEAR(t1.date) = YEAR(t2.date) AND MONTH(t1.date) = MONTH(t2.date)
WHERE t2.post_id = 1
GROUP BY year, month

DEMO

Get the rank of a specific row, by month of the year

1 Answers1