Patrick Gregorio Patrick Gregorio - 3 months ago 9
SQL Question

SQL Get Current Revision

I have a table that records revisions for another table:

Revisions (r)



id protocol year revision_created
----------------------------------------------------
100 PR00001 2015 7/29/2015 10:00:00 AM
101 PR00001 2015 8/29/2015 09:00:00 AM
102 PR00001 2016 2/12/2016 09:30:00 AM
250 PR00002 2016 3/10/2016 10:00:00 AM
251 PR00002 2016 3/10/2016 04:00:00 PM
252 PR00002 2016 8/31/2016 02:48:00 PM


I wanted to get the
id
of all the latest revision for each protocol for each year so in this case I wanted to get:

id year protocol
--------------------------
101 2015 PR00001
102 2016 PR00001
252 2016 PR00002


I have this query and it works but I feel like I shouldn't be using
MAX
on the
id
field but instead use it on the
revision_created
field - but if I do that then I won't be getting the
id
. Thoughts or suggestions?

SELECT MAX(r.id) AS id, r.year, r.protocol
FROM revision r
GROUP BY r.year, r.protocol

Answer

You can use ROW_NUMBER:

SELECT id, year, protocol
FROM (  SELECT *,
               ROW_NUMBER() OVER(PARTITION BY protocol, year ORDER BY revision_created DESC) RN
        FROM revision) AS r
WHERE RN = 1

If you can't use ROW_NUMBER, you can join with the same table:

SELECT r1.id, r1.year, r1.protocol
FROM revision r1
INNER JOIN (SELECT  protocol,
                    year,
                    MAX(revision_created) Max_Revision
            FROM revision
            GROUP BY protocol,
                     year) r2
    ON r1.protocol = r2.protocol
    AND r1.year = r2.year
    AND r1.revision_created = r2.Max_Revision