CODEWITHSUNDEEP
mysqlsql
user1298416
user1298416

Reputation: 341

SQL - need a query for the last_date for each user, max_date, min_date

The original table looks like:

id   date        name
----------------------
11   01-2021     'aaa'
11   03-2020     'bbb'
11   01-2019     'ccc'
11   12-2017     'ddd'
12   02-2011     'kkk'
12   05-2015     'lll'
12   12-2020     'mmm'

the expected output:

id.   min_date.  max_date   name
---------------------------------
11    12-2017    01-2021    'aaa'
12    02-2011    12-2020    'mmm'

I need to have, min, max dates and the name that corresponds to the max_date. I know a way to get min, max dates and separately how to get the date corresponding to the max_date (using ROW_NUMBER() OVER(PARTITION BY...)), but cannot figure out how to combine both together.

Upvotes: 0

Views: 44

Answers (2)

hoangnh
hoangnh

Reputation: 249

This below query should work

SELECT *
FROM tbl1 t1
INNER JOIN
  (SELECT id,
          min(date) AS min_date,
          max(date) AS max_date
   FROM tbl1
   GROUP BY id) t2 ON t1.date = t2.max_date

Demo

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521987

One option is to use ROW_NUMBER along with pivoting logic to select the name corresponding the max date per each id:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) rn
    FROM yourTable
)

SELECT
    id,
    MIN(date) AS min_date,
    MAX(date) AS max_date,
    MAX(CASE WHEN rn = 1 THEN name END) AS name
FROM cte
GROUP BY
    id;

screen capture from demo link below

Demo

Note that your current date column appears to be text. Don't store your dates as text, instead use a proper date column.

Upvotes: 1

Related Questions