CODEWITHSUNDEEP
mysql
Zoe
Zoe

Reputation: 2169

Return unique rows of records for each user

I have a table that tracks different qualifications for students. The qualifications are renewed periodically, so many students have multiple records for the same qualification.

I'm trying to return just the most recent record of each qualification for each student, without the duplicates.

So far I have this, but I'm stuck on what I need to do to remove the duplicate type_scrn and just return the most recent records.

SELECT scrn.*
FROM cert_scrn scrn
WHERE scrn.id_scrn = (SELECT scrn2.id_scrn 
    FROM cert_scrn scrn2
    WHERE scrn.id_scrn = scrn2.id_scrn
    AND ( scrn2.type_scrn = 1
        OR scrn2.type_scrn = 11
        OR scrn2.type_scrn = 12
        OR scrn2.type_scrn = 13 )
    ORDER BY scrn2.expiredate_scrn DESC  LIMIT 1) 
ORDER BY  scrn.idstu_scrn

This returns:

id_scrn | idstu_scrn | type_scrn | expiredate_scrn
-------------------------------------------------
15      | 58         | 1         | 2010-01-26
1539    | 58         | 1         | 2015-06-21
5790    | 58         | 11        | 2016-02-20
5791    | 58         | 12        | 2016-02-20
5792    | 58         | 13        | 2016-02-20

What I need returned:

id_scrn | idstu_scrn | type_scrn | expiredate_scrn
---------------------------------------------------
1539    | 58         | 1         | 2015-06-21
5790    | 58         | 11        | 2016-02-20
5791    | 58         | 12        | 2016-02-20
5792    | 58         | 13        | 2016-02-20

Upvotes: 0

Views: 65

Answers (2)

Rajesh
Rajesh

Reputation: 2155

Please check this one based on Primary Key, appears working fine for this scenario :

SELECT * FROM cert_scrn WHERE id_scrn IN 
(SELECT MAX(id_scrn) FROM cert_scrn GROUP BY idstu_scrn, type_scrn);

Upvotes: 0

pala_
pala_

Reputation: 9010

You need to join to a subquery which finds the max date for each idstu_scn, type_scrn group.

So your query to get the max(date) would be:

select idstu_scrn, type_scrn, max(expiredate_scrn) mdate
  from cert_scrn
  group by idstu_scrn, type_scrn

Which we then just need to join back to the cert_scrn table again, to find the rest of the details to go along with it.

select scrn.*
  from cert_scrn scrn
    inner join (
      select idstu_scrn, type_scrn, max(expiredate_scrn) mdate
        from cert_scrn
        group by idstu_scrn, type_scrn ) q
      on scrn.idstu_scrn = q.idstu_scrn and scrn.type_scrn = q.type_scrn and scrn.expiredate_scrn = q.mdate
  where scrn.type_scrn = 1
    or scrn.type_scrn = 11
    or scrn.type_scrn = 12
    or scrn.type_scrn = 13

demo fiddle here

Upvotes: 1

Related Questions