SQL Select Minimum Value And Add Back To Table-CodePudding

TABLE1
STUDENT SUBJECT DATE    WANT
1   HISTORY 1/1/2020    11/3/2019
1   HISTORY 11/3/2019   11/3/2019
1   HISTORY 12/1/2021   11/3/2019
2   HISTORY 3/4/2019    3/4/2019
2   HISTORY 2/6/2021    3/4/2019
3   HISTORY 1/8/2022    1/8/2022

I Have TABLE1 which has STUDENT SUBJECT and DATE and I wish to add COLUMN, WANT which takes the MINIMUM(DATE) for each STUDENT.

I can do:

SELECT STUDENT, SUBJECT, MIN(DATE) AS WANT
FROM TABLE1
GROUP BY STUDENT

which gives me

STUDENT MIN(DATE)
1   11/3/2019
2   3/4/2019
2   1/8/2022

but How I add it back to get the desire output?

CodePudding user response：

If you want to update your existing table, you could run the following UPDATE statement where you JOIN the source table with your SELECT query:

UPDATE TABLE1 t1
JOIN (
    SELECT STUDENT, SUBJECT, MIN(DATE) AS WANT
    FROM TABLE1
    GROUP BY STUDENT
) t2
ON t1.STUDENT = t2.STUDENT

SET t1.DATE = t2.WANT

If instead you want to insert your query into a new table instead of updating, you could run the following query:

INSERT INTO NEWTABLE
SELECT STUDENT, SUBJECT, MIN(DATE) AS WANT
FROM TABLE1
GROUP BY STUDENT

Note NEWTABLE would need to be created first before running the above query.

CodePudding user response：

You can use the window function with MIN statement.

SELECT STUDENT, 
       SUBJECT, 
       DATE,
       MIN(DATE) OVER (PARTITION BY STUDENT) AS WANT
FROM TABLE1

CodePudding user response：

WITH YOUR_TABLE(STUDENT, SUBJECT, DATE) AS
(
  SELECT 1,   'HISTORY', '2020-01-01'  UNION ALL
  SELECT 1,   'HISTORY', '2019-11-04'UNION ALL
  SELECT 1,   'HISTORY', '2021-12-01'  UNION ALL
  SELECT 2,   'HISTORY', '2019-03-04' UNION ALL  
  SELECT 2,   'HISTORY','2019-02-06' UNION ALL   
  SELECT 3,   'HISTORY', '2022-01-08'   
)
SELECT T.STUDENT,T.SUBJECT,T.DATE,
  MIN(T.DATE)OVER(PARTITION BY T.STUDENT ORDER BY T.DATE ASC)AS WANT
 FROM YOUR_TABLE AS T

I guess, you can use something like this. And please specify DATE always in ISO-format "YYYYMMDD" or "YYYY-MM-DD"