CODEWITHSUNDEEP
sqloracle-database
Anup Deuja
Anup Deuja

Reputation: 63

Oracle: Select a distinct column based on the value of a different column

This is what the current table looks like.

item description date
12-001 SIM CARD 04/06/2021
12-001 SIM CARD-1 04/07/2021

I want to get the latest distinct item from the above table. The record that I am trying to get will look like this since this record has the latest date.

item description date
12-001 SIM CARD-1 04/07/2021

I'm using Oracle. Below is my query. Any help is appreciated.

SELECT DISTINCT item , description, date FROM items

Upvotes: 0

Views: 59

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

On recent (12.x and greater) versions of Oracle you can use something like:

SELECT *
  FROM ITEMS
  ORDER BY "date" DESC
  FETCH FIRST 1 ROWS ONLY

Note that "date" is quoted in this query because date is not a valid identifier in Oracle unless quoted, as it's also the name of a data type, the ANSI date specifier, and probably some other things I've forgotten.

db<>fiddle here

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Since your description column has different data so DISTINCT keyword would not work here. You may try below query with window function -

SELECT item , description, date
  FROM (SELECT item, description, date, ROW_NUMBER() OVER(PARTITION BY item ORDER BY date DESC) RN
          FROM items)
 WHERE RN = 1;

Upvotes: 0

The Impaler
The Impaler

Reputation: 48750

You can use ROW_NUMBER() to identify the rows you want.

For example:

select item, description, date
from (
  select i.*,
    row_number() over(partition by item order by date desc) as rn
  from items i
) x
where rn = 1

In this case only the last row for every item will have the row number as 1.

Upvotes: 1

Related Questions