CODEWITHSUNDEEP
sqlgroup-bymin
bvowe
bvowe

Reputation: 3384

SQL Select Minimum Value And Add Back To Table

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?

Upvotes: 0

Views: 56

Answers (1)

Sean Anglim
Sean Anglim

Reputation: 179

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.

Upvotes: 1

Related Questions