mysql row number count down and dynamic number of row

Question

I believe it can be solve by temp table/stored procedure but in case it can be done by single SQL statement.

Goal: List all row with count down by year, however number of row of each year is different. Row can be order by date

Result Arm to:

|-Count Down-|-Date-------|
| 3          | 2013-01-01 | <- Start with number of Row of each year
| 2          | 2013-03-15 |
| 1          | 2013-06-07 |
| 5          | 2014-01-01 | <- Start with number of Row of each year
| 4          | 2014-03-17 |
| 3          | 2014-07-11 |
| 2          | 2014-08-05 |
| 1          | 2014-11-12 |

SQL:

Select @row_number:=@row_number-1 AS CountDown, Date
FROM table JOIN 
    (Select @row_number:=COUNT(*), year(date) FROM table GROUP BY year(date))

Is there any solution for that?

Answer 1

The subquery that gets the count by year needs to return the year, so you can join it with the main table to get the starting number for the countdown. And you need to detect when the year changes, so you need another variable for that.

SELECT @row_number := IF(YEAR(d.Date) = @prevYear, @row_number-1, y.c) AS CountDown,
       d.Date, @prevYear := YEAR(d.Date)
FROM (SELECT Date 
      FROM Table1 
      ORDER BY Date) AS d
JOIN 
    (Select count(*) AS c, year(date) AS year
     FROM Table1 
     GROUP BY year(date)) AS y
ON YEAR(d.Date) = y.year
CROSS JOIN (SELECT @prevYear := NULL) AS x

DEMO