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

Mysql – SQL Flag By Group

CREATE TABLE t
    (STUDENT int, SCORE int, DATE date)
;
    
INSERT INTO t
    (STUDENT, SCORE, DATE)
VALUES
    (1, 6, '2022-02-01 00:00:00'),
    (1, 2, '2022-03-12 00:00:00'),
    (1, 5, '2022-04-30 00:00:00'),
    (2, 2, '2022-04-12 00:00:00'),
    (2, 0, '2022-04-17 00:00:00'),
    (2, 7, '2022-05-08 00:00:00'),
    (3, 2, '2022-03-16 00:00:00'),
    (3, 6, '2022-03-18 00:00:00'),
    (3, 2, '2022-04-02 00:00:00'),
    (3, 9, '2022-04-27 00:00:00'),
    (4, 4, '2022-02-24 00:00:00'),
    (4, 0, '2022-02-26 00:00:00'),
    (5, 3, '2022-01-28 00:00:00'),
    (5, 0, '2022-02-21 00:00:00'),
    (5, 4, '2022-04-05 00:00:00')
;

I wish to make a file like this:

SELECT STUDENT, min(DATE) as min_date from t where SCORE = 2 group by STUDENT

The rules is:

  1. Find min(DATE) when SCORE = 2 and compare to min(DATE) when SCORE = 6
    A. If min(DATE, SCORE = 2) < min(DATE, SCORE = 6) then FLAG equals to 1
    B. If min(DATE, SCORE = 2) >= min(DATE, SCORE = 6) then FLAG equals to 0
    A. If STUDENT has record for SCORE = 2 but no record for SCORE = 6, then FLAG equals to 1
Tags:

2

Answers


  1. 0

    Here you use the same approach with LEFT JOIN

    SELECT t1.STUDENT,
CASE WHEN t2.min_date < t6.min_date OR t6.min_date IS NULL THEN 1
ELSE 0 END
FROM (SELECT STUDENT, min(DATE) as min_date
from t
where SCORE = 2
group by STUDENT) t1 LEFT JOIN 
(SELECT STUDENT, min(DATE) as min_date
from t
where SCORE = 6
group by STUDENT) t6 ON t1.STUDENT = t2.STUDENT
    Login or Signup to reply.
  2. 0

    You can use here – MySQL CASE statement

    Below is the query to get the desired result.

    WITH Score2And6 AS (
SELECT t.STUDENT,
       MIN(CASE WHEN t.SCORE = 2 THEN t.DATE ELSE NULL END) AS min_date_score_2,
       MIN(CASE WHEN t.SCORE = 6 THEN t.DATE ELSE NULL END) AS min_date_score_6
FROM t
WHERE t.SCORE IN (2, 6)
GROUP BY t.STUDENT)


SELECT score.STUDENT,
   CASE WHEN score.min_date_score_2 < score.min_date_score_6 OR score.min_date_score_6 IS NULL THEN 1
        ELSE 0
   END AS FLAG 
FROM Score2And6 score 
ORDER BY STUDENT ASC;

    Here, I have used a CTE (Common Table Expression) to make the query more readable.

    Demo: https://onecompiler.com/mysql/3zpma8neh

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search