Return line of data with most recent date

Question

I'm using Oracle SQL and trying to return the most recent category dependent on the most_recent_date. I am able to successfully pull the most_recent_date per category per part no/niin, but I'm not able to get the full results I need.

I have the below query:

SELECT DISTINCT
    contract,
    part_no,
    niin,
    category,
    MAX(date_entered) OVER(PARTITION BY contract, part_no, niin, category) most_recent_date
FROM
    ( **inner query** )

Current result:

CONTRACT    PN  NIIN 1T 01-AUG-08
CONTRACT    PN  NIIN 7T 19-APR-22

My End result for this needs to be just the line that has the most recent date. Line needed below:

CONTRACT    PN  NIIN 7T 19-APR-22

I have tried to nest the select statement again, to then try and choose the most recent line, but still received the same 2 lines. I also tried to use most_recent_date within a grouping statement for category and that didn't work either.

I'm sure it's a simple fix, but I'm not finding it.

Horaciux · Answer 1 · 2023-06-15T15:37:31.333

1

The ROW_NUMBER function assigns a number to each row based on the order of the dates they were entered, starting from 1 for each category. So, the row with the most recent date within each category will have the number 1.

In the outer query, we select only the rows where the assigned number is 1. This means we are choosing the row with the most recent date for each category.

    SELECT CONTRACT, PN, NIIN, CATEGORY, DATE_ENTERED
FROM (
    SELECT
        CONTRACT,
        PN,
        NIIN,
        CATEGORY,
        DATE_ENTERED,
        ROW_NUMBER() OVER (PARTITION BY CONTRACT, PN, NIIN, CATEGORY ORDER BY DATE_ENTERED DESC) AS rn
    FROM
        ( **inner query** )
) subquery
WHERE rn = 1;

edited Jun 15 '23 at 15:37

answered Jun 15 '23 at 13:41

Horaciux

6,322
2
22
41

1

The partition by should be `PARTITION BY contract, part_no, niin, category` ! – SelVazi Jun 15 '23 at 13:56
This partially worked, but I had to tweak it to have the contract and PN be in the row_number aggregation. 1T and 7T are the only options so the above didn't allow me to see the most recent category on a part/niin level. – KassieB Jun 15 '23 at 13:56
@SelVazi fixed! thanks for commenting – Horaciux Jun 15 '23 at 15:38

score 1 · Accepted Answer · answered Jun 15 '23 at 13:49

In Oracle, you could use group by and the keep syntax to bring the category that corresponds to the latest date:

select contract, part_no, niin, 
    max(date_entered) last_date,
    max(category) keep(dense_rank last order by date_entered) last_category
from ....
group by contract, part_no, niin

Return line of data with most recent date

2 Answers2