CODEWITHSUNDEEP
mysql
Rodrick
Rodrick

Reputation: 615

How to get max id from each category on mysql?

I need to get the validity of the certifications of a person. A person could have 2 o more certifications. I was ableto get the last state of a certificaction using max(cert_id) but that shows the state of just one kind of certification. How can I get the max(CERT_ID) of each kind of CERTIFICATION?

+-----------+---------+-------------------+-----------+-------------+----------+
| PERSON_ID | CERT_ID | CERTIFICATION     | STATE     | EXPIRE_DATE | VALIDITY |
+-----------+---------+-------------------+-----------+-------------+----------+
| 111       | 53961   | CERTIFICATION ONE | DELIVERED | 09-05-2018  | EXPIRED  |
+-----------+---------+-------------------+-----------+-------------+----------+
| 111       | 88196   | CERTIFICATION ONE | DELIVERED | 16-03-2020  | VALID    |
+-----------+---------+-------------------+-----------+-------------+----------+
| 111       | 96943   | CERTIFICATION TWO | DELIVERED | 04-12-2020  | VALID    |
+-----------+---------+-------------------+-----------+-------------+----------+

In this case I would like to get the following result:

+-----------+---------+-------------------+-----------+-------------+----------+
| PERSON_ID | CERT_ID | CERTIFICATION     | STATE     | EXPIRE_DATE | VALIDITY |
+-----------+---------+-------------------+-----------+-------------+----------+
| 111       | 88196   | CERTIFICATION ONE | DELIVERED | 16-03-2020  | VALID    |
+-----------+---------+-------------------+-----------+-------------+----------+
| 111       | 96943   | CERTIFICATION TWO | DELIVERED | 04-12-2020  | VALID    |
+-----------+---------+-------------------+-----------+-------------+----------+

More Info: Some times the validity could be all expired or valid for each kind of certificaction depending on the expire date of each certification.

UPDATE: This is a simplification of what I looking for. This is the actual query that I using but just show me 1 result because I am using max_id. Don't know how to group correctly by certification.

SELECT 
    PERSON_ID,
    WORKER,
    job,
    MAX(CRED_ID) AS CRED_ID,
    CREDENTIAL,
    date_1,
    date_2,
    date_3,
    IF(date_2 = 'Not requerided',
        IF(STR_TO_DATE(date_1, '%d-%m-%Y') <= STR_TO_DATE(date_3, '%d-%m-%Y'),
            date_1,
            date_3),
        CASE
            WHEN
                STR_TO_DATE(date_1, '%d-%m-%Y') <= STR_TO_DATE(date_2, '%d-%m-%Y')
                    AND STR_TO_DATE(date_1, '%d-%m-%Y') <= STR_TO_DATE(date_3, '%d-%m-%Y')
            THEN
                date_1
            WHEN
                STR_TO_DATE(date_2, '%d-%m-%Y') <= STR_TO_DATE(date_1, '%d-%m-%Y')
                    AND STR_TO_DATE(date_2, '%d-%m-%Y') <= STR_TO_DATE(date_3, '%d-%m-%Y')
            THEN
                date_2
            ELSE date_3
        END) AS END_DATE,
    STATUS
FROM
    (SELECT 
        t.PERSON_ID,
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT(t.NAME, ' ', t.LASTNAME), 'Á', 'A'), 'É', 'E'), 'Í', 'I'), 'Ó', 'O'), 'Ú', 'Ú'), 'Ñ', 'N') AS WORKER,
            (SELECT 
                    CONCAT(ca.name, ' - ', c.name)
                FROM
                    job c
                INNER JOIN job_agrupado ca ON c.agrupado_id = ca.agrupado_id
                WHERE
                    c.job_id = cl.job_id) AS job,
            cl.laboral_id AS CRED_ID,
            c.cred_id AS CREDENTIAL,
            (SELECT 
                    IF(exam.OLD = 1, DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.EVALUATION_DATE, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(exam.EXPIRE_DATE = ''
                            OR exam.EXPIRE_DATE IS NULL, DATE_FORMAT(DATE_ADD(exam.EVALUATION_DATE, INTERVAL exam.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y')))) AS EXPIRATION
                FROM
                    eva_competences revalidation
                LEFT JOIN eva_competences exam ON (revalidation.recognized = exam.EVA_LAB_ID)
                WHERE
                    revalidation.TIPO_EVA_ID = 1
                        AND revalidation.LABORAL_ID = cl.laboral_id
                LIMIT 1) AS date_1,
            IFNULL((SELECT 
                    IF(exam.OLD = 1, DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.EVALUATION_DATE, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(exam.EXPIRE_DATE = ''
                            OR exam.EXPIRE_DATE IS NULL, DATE_FORMAT(DATE_ADD(exam.EVALUATION_DATE, INTERVAL exam.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y')))) AS EXPIRATION
                FROM
                    eva_competences revalidation
                LEFT JOIN eva_competences exam ON (revalidation.recognized = exam.EVA_LAB_ID)
                WHERE
                    revalidation.TIPO_EVA_ID = 2
                        AND revalidation.LABORAL_ID = cl.laboral_id
                LIMIT 1), 'No Requerido') AS date_2,
            (SELECT 
                    IF(exam.OLD = 1, DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.EVALUATION_DATE, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(exam.EXPIRE_DATE = ''
                            OR exam.EXPIRE_DATE IS NULL, DATE_FORMAT(DATE_ADD(exam.EVALUATION_DATE, INTERVAL exam.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y')))) AS EXPIRATION
                FROM
                    eva_competences revalidation
                LEFT JOIN eva_competences exam ON (revalidation.recognized = exam.EVA_LAB_ID)
                WHERE
                    revalidation.TIPO_EVA_ID = 3
                        AND revalidation.LABORAL_ID = cl.laboral_id
                LIMIT 1) AS date_3,
            (SELECT 
                    MIN(IF(exam.OLD = 1, DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.EVALUATION_DATE, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(exam.EXPIRE_DATE = ''
                            OR exam.EXPIRE_DATE IS NULL, DATE_FORMAT(DATE_ADD(exam.EVALUATION_DATE, INTERVAL exam.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y'))))) AS END_DATE
                FROM
                    eva_competences revalidation
                LEFT JOIN eva_competences exam ON (revalidation.recognized = exam.EVA_LAB_ID)
                WHERE
                    revalidation.LABORAL_ID = cl.laboral_id) AS END_DATE,
            (SELECT 
                    IF(MIN(IF(exam.OLD = 1, DATE_FORMAT(exam.EXPIRE_DATE, '%Y-%m-%d'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.EVALUATION_DATE, INTERVAL revalidation.VALIDITY YEAR), '%Y-%m-%d'), IF(exam.EXPIRE_DATE = ''
                            OR exam.EXPIRE_DATE IS NULL, DATE_FORMAT(DATE_ADD(exam.EVALUATION_DATE, INTERVAL exam.VALIDITY YEAR), '%Y-%m-%d'), DATE_FORMAT(exam.EXPIRE_DATE, '%Y-%m-%d'))))) > CURDATE(), 'VALID', 'EXPIRED')
                FROM
                    eva_competences revalidation
                LEFT JOIN eva_competences exam ON (revalidation.recognized = exam.EVA_LAB_ID)
                WHERE
                    revalidation.LABORAL_ID = cl.laboral_id) AS STATUS
    FROM
        competences_laborales cl
    LEFT JOIN WORKERS t ON (cl.WORKER_ID = t.WORKER_ID)
    LEFT JOIN CREDENTIALS c ON (c.lab_id = cl.laboral_id)
    WHERE
        t.PERSON_ID = '111'
            AND cl.STATUS IN (12 , 13, 7, 8, 9)
    ORDER BY PERSON_ID , CRED_ID DESC) AS table2
GROUP BY PERSON_ID;

UPDATE 2: The inner query gives me the following result:

+-----------+---------------+---------------------+---------+------------+------------+------------+------------+------------+---------+
| PERSON_ID | WORKER        | CERTIFICATION (JOB) | CRED_ID | CREDENTIAL | DATE_1     | DATE_2     | DATE_3     | END_DATE   | STATUS  |
+-----------+---------------+---------------------+---------+------------+------------+------------+------------+------------+---------+
| 111       | NAME LASTNAME | CERTIFICATION ONE   | 53961   | 53961      | 14-02-2017 | 07-02-2017 | 21-02-2017 | 07-02-2017 | EXPIRED |
+-----------+---------------+---------------------+---------+------------+------------+------------+------------+------------+---------+
| 111       | NAME LASTNAME | CERTIFICATION TWO   | 88196   | 88196      | 08-02-2020 | 09-02-2021 | 09-02-2021 | 08-02-2020 | VALID   |
+-----------+---------------+---------------------+---------+------------+------------+------------+------------+------------+---------+
| 111       | NAME LASTNAME | CERTIFICATION TWO   | 96943   | 96943      | 23-03-2020 | 31-03-2019 | 21-02-2020 | 31-03-2019 | VALID   |
+-----------+---------------+---------------------+---------+------------+------------+------------+------------+------------+---------+

The outer query is just to try to get the validity of the newest certifications grouped by type of certifications and also it gets the min date of the 3 dates.

Upvotes: 0

Views: 39

Answers (1)

Nick
Nick

Reputation: 147146

You can achieve your desired results by JOINing the table to a derived table of maximum value of CERT_ID grouped by CERTIFICATION:

SELECT c1.*
FROM certifications c1
JOIN (SELECT CERTIFICATION, MAX(CERT_ID) AS MAX_CERT_ID
      FROM certifications
      GROUP BY CERTIFICATION) c2 ON c2.CERTIFICATION = c1.CERTIFICATION AND c2.MAX_CERT_ID = c1.CERT_ID

Output:

PERSON_ID   CERT_ID CERTIFICATION       STATE       EXPIRE_DATE VALIDITY
111         88196   CERTIFICATION ONE   DELIVERED   16-03-2020  VALID
111         96943   CERTIFICATION TWO   DELIVERED   04-12-2020  VALID

Demo on dbfiddle

Upvotes: 2

Related Questions