SQL Select MAX/MIN On Condition-CodePudding

TABLE1
    STUDENT SUBJECT DATE
    1   ENGLISH 2/14/2021
    1   ENGLISH 4/24/2021
    1   ENGLISH 9/20/2022
    1   ENGLISH 11/14/2022
    1   HISTORY 12/15/2021
    1   HISTORY 3/3/2022
    1   HISTORY 3/12/2022
    2   ENGLISH 1/29/2021
    2   ENGLISH 4/20/2021
    2   ENGLISH 8/29/2022
    2   ART 3/24/2021
    3   ART 6/21/2019

TABLE2

STUDENT DATE    LAST_ENGLISH    ANY_HISTORY ANY_OTHER
1   2/14/2021   2/14/2021   1   0
1   4/24/2021   2/14/2021   1   0
1   9/20/2022   2/14/2021   1   0
1   11/14/2022  2/14/2021   1   0
2   1/29/2021   1/29/2021   0   1
2   4/20/2021   1/29/2021   0   1
2   8/29/2022   1/29/2021   0   1
2   3/24/2021   1/29/2021   0   1

I current have TABLE1 and wish to create TABLE2 where

LAST_ENGLISH = MAXIMUM(DATE) for each STUDENT when SUBJECT = ENGLISH

ANY_HISTORY = 1 for each STUDENT if SUBJECT ever equals to HISTORY, otherwise 0

ANY_OTHER = 1 for each STUDENT if SUBJECT ever equals ART or SCIECNE, otherwise 0

And then TABLE2 is one row for every time a STUDENT had ENGLISH with the other values that are constant for each STUDENT.

TABLE2 is restrict only to STUDENT who ever took ENGLISH

I try this:

 SELECT STUDENT, DATE,  case when SUBJECT = 'ENGLISH', MAX(DATE) else
 NULL as LAST_ENGLISH, case when SUBJECT = 'HISTORY', 1 else 0 as
 ANY_HISTORY, case when SUBJECT = 'ART' OR SUBJECT = 'SCIENCE', 1 else
 0 as ANY_OTHER FROM TABLE1 GROUP BY STUDENT

CodePudding user response：

SELECT STUDENT, DATE, 
    MAX(CASE WHEN SUBJECT = 'ENGLISH' THEN DATE END) AS LAST_ENGLISH,
    MAX(CASE WHEN SUBJECT = 'HISTORY' THEN 1 ELSE 0 END) AS ANY_HISTORY,
    MAX(CASE WHEN SUBJECT IN ('ART', 'SCIENCE') THEN 1 ELSE 0 END) AS ANY_OTHER
FROM TABLE1
GROUP BY STUDENT, DATE

CASE expression checks whether the SUBJECT is either ART or SCIENCE, and if it is, it outputs a 1. Otherwise, it outputs a 0. The MAX function is used to select the maximum value in each group, which will be either 0 or 1 depending on whether any rows in the group have SUBJECT equal to either ART or SCIENCE

CodePudding user response：

Maybe this works for you? If I've understood your question correctly

SELECT
    t1.student,
    MAX(t1.date) AS date,
     (SELECT MAX(date) FROM TABLE1 AS t2 WHERE t2.student = t1.student GROUP BY t2.student HAVING SUM(t2.subject = 'ENGLISH')) AS last_english,
     (SELECT MAX(CASE WHEN subject = 'HISTORY' THEN 1 ELSE 0 END) FROM TABLE1 AS t2 WHERE t2.student = t1.student) AS any_history,
     (SELECT MAX(CASE WHEN subject IN ('ART', 'SCIENCE') THEN 1 ELSE 0 END) FROM TABLE1 AS t2 WHERE t2.student = t1.student) AS any_other
FROM TABLE1 AS t1
GROUP BY t1.date,t1.student
HAVING last_english IS NOT NULL
ORDER BY student ASC

Here is a runable example, if something is wrong its very possible i'e misunderstood what you're looking for

http://sqlfiddle.com/#!9/2478a1/52

I have now matched this exactly to the output you've provided