Mysql Question posted in
The official MySQL documentation can be found here.

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

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    SQL query that selects one user with the maximum value of prince per year, achieving your desired result:

    SELECT e.first_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 year
HAVING SUM(e.prince) = (
    SELECT MAX(SUM(e.prince))
    FROM (
        SELECT * FROM employee WHERE termination IS NULL
    ) e
    JOIN storage s ON e.id = s.eid
    GROUP BY YEAR(e.hire_date)
)
ORDER BY year;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search