CODEWITHSUNDEEP
sqloracle-databasegroup-bymax
dokgu
dokgu

Reputation: 6040

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

Upvotes: 0

Views: 178

Answers (2)

Lamak
Lamak

Reputation: 70648

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269893

In Oracle, you can use an aggregation and keep:

SELECT r.protocol, r.year,
       MAX(id) KEEP (DENSE_RANK FIRST ORDER BY r.revision_created DESC) as id
FROM revision r
GROUP BY r.year, r.protocol;

Upvotes: 1

Related Questions