CODEWITHSUNDEEP
oracle-databasedistinct
Jamie Taylor
Jamie Taylor

Reputation: 3530

Oracle - Select the latest distinct record from table

In theory this seems easy but I just can't get my head around this...

Here's my table info and data

ID    Heading    Date         Row_Num
 1       Test    12-MAR-12          1
 1      Test2    13-MAR-12          2
 2      Test3    13-MAR-12          3

And here's my query

select * from 
table1 
where date <= :date 
order by date desc

:date being 14-MAR-12 for instance

This obviously produces all three results but what I want to achieve from this query is the following data

ID    Heading    Date         Row_Num
 1      Test2    13-MAR-12          2
 2      Test3    13-MAR-12          3

So I added in a distinct but this doesn't work as expected as I have to group by each column and by doing that it produces 3 results still.

Any ideas on how I can accomplish the result i'm after?

Upvotes: 3

Views: 11847

Answers (3)

jake stayman
jake stayman

Reputation: 1808

I could be misunderstanding what you want to achieve, but I think you can do it with a group by and a subquery, as follows:

SELECT * 
FROM table1 
WHERE ID||date IN 
  (SELECT ID||MAX(date)
  FROM table1 
  WHERE date <= to_date('14-mar-12', 'DD-mon-YY') 
  GROUP BY ID
  );

This will give you one row per ID and Date, as long as there are not multiple rows with the same ID and same date.

Upvotes: 1

Jordan Parmer
Jordan Parmer

Reputation: 37164

You'll need to use an analytic function. The row_number function should do the trick.

select *
from
(
    select 
        id,
        heading,
        date,
        row_number() over ( partition by id order by heading desc nulls last ) r
    from table1
)
where r = 1

Analytic functions can be used to perform analysis and grouping of rows within a result set. In the example above, the inner-select queries table1 as usual. The row_number() function will compare each row with other returned rows (using the aggregate provided in the partition statement) to produce a number. In this case, we are grouping by ID and ordering by heading descending. The outer-select has a where clause that filters out rows that don't have a row_number of 1. Run just the inner-select to see how the whole thing works.

Upvotes: 7

Justin Cave
Justin Cave

Reputation: 231651

If you just want the row with the latest DT for each ID, you would want something like

SELECT id,
       heading,
       dt
  FROM (SELECT id,
               heading,
               dt,
               ROW_NUMBER() OVER (PARTITION BY id
                                      ORDER BY dt desc) rnk
          FROM table1)
 WHERE rnk = 1

If ties are possible (two rows with the same ID and DT, you may want to add something else to the ORDER BY in the analytic function that breaks the tie deterministically rather than letting Oracle break it arbitrarily.

Upvotes: 3

Related Questions