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?

Why don't you start by allowing a more precise Date format ? It's impossible for you to distinguish which is the last document created. And then you ll be able to add 'LIMIT 1' at the end of your query — jozinho22, Mar 16 '21 at 12:41
@jozinho22 Oracle does not support the `LIMIT` keyword (instead, either use `FETCH FIRST n ROWS ONLY` or, depending on version, filter on the `ROWNUM` pseudo-column after ordering). However, none of those would limit the rows in each group; they would limit the total number of rows. — MT0, Mar 16 '21 at 13:05

score 0 · Accepted Answer · answered Mar 16 '21 at 12:21

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

How to find last item by parameter (SQL)?

1 Answers1