How to find last item by parameter (SQL)?

Question

I have a query to search for some itens in a history. Ex:

SELECT * FROM HISTORY WHERE DOCUMENT IN ('DOC1','DOC2','DOC3') ORDER BY DOC ASC, CREATION_DATE DESC;

This query return me a list like this:

ID | DOC | CREATIONDATE

6  | DOC1 | 12/12/2022

3  | DOC1 | 11/11/2022

5  | DOC2 | 12/12/2022

2  | DOC2 | 11/11/2022

4  | DOC3 | 12/12/2022

1  | DOC3 | 11/11/2022

But I want just the most recent item by doc. For example:

ID | DOC | CREATIONDATE

6  | DOC1 | 12/12/2022

5  | DOC2 | 12/12/2022

4  | DOC3 | 12/12/2022

I dont want the old itens. Just last item inserted by document. Someone can help me?

Answer 1

One option is to rank them by creation_date in descending order, and pick the first one.

  SELECT id, doc, creation_date
    FROM (SELECT id,
                 doc,
                 creation_date,
                 RANK () OVER (PARTITION BY doc ORDER BY creation_date DESC) rnk
            FROM history
           WHERE document IN ('DOC1', 'DOC2', 'DOC3'))
   WHERE rnk = 1
ORDER BY doc