sqlmysqlmysql-8.0

MySQL - How to select one user with max value per year


SELECT e.frist_name, SUM(e.prince), YEAR(e.hire_date) AS year FROM (
   SELECT * FROM employee WHERE termination IS NULL
) e
JOIN storage s ON e.id = s.eid GROUP BY e.id, e.hire_date;

After SQL query I have a table like this:

+--------------+---------------+------+
| e.first_name | SUM(e.prince) | year |
+--------------+---------------+------+
| Johnn        | 1450          | 2012 |
+--------------+---------------+------+
| Emma         | 1020          | 2013 |
+--------------+---------------+------+
| Gordon       | 340           | 2012 |
+--------------+---------------+------+
| Ave          | 600           | 2014 |
+--------------+---------------+------+

My desired result is something like this (only one user per year with max value of prince):

+--------------+---------------+------+
| e.first_name | SUM(e.prince) | year |
+--------------+---------------+------+
| Johnn        | 1450          | 2012 |
+--------------+---------------+------+
| Emma         | 1020          | 2013 |
+--------------+---------------+------+
| Ave          | 600           | 2014 |
+--------------+---------------+------+

Solution

  • You can use ROW_NUMBER with a CTE

    WITH CTE as (
    
        SELECT e.frist_name, SUM(e.prince) sum_ , YEAR(e.hire_date) AS year
        , ROW_NUMBER() OVER(PARTITION BY YEAR(e.hire_date) ORDER BY SUM(e.prince) DESC) rn   
        FROM (
           SELECT * FROM employee WHERE termination IS NULL
        ) e
        JOIN storage s ON e.id = s.eid 
        GROUP BY e.id,e.frist_name, YEAR(e.hire_date) )
        SELECT 
            frist_name, sum_, year
        FROM CTE
        WHERE rn = 1