CODEWITHSUNDEEP
oracle-database
user441521
user441521

Reputation: 6998

oracle sql to take the first record in a group by clause

I have a table that has 2 columns.

ID number Created_DT date

I can have duplicate values in ID (and often do). I need to get just 1 record per ID and I need the Created_DT so I do:

select ID, Created_DT from table group by ID, Created_DT

However, the Created_DT has time in it as well and for the same ID can span over 2 seconds making it unique and returning 2 records instead of 1. This is such a small difference that I don't care about it. I would want to just get the first one if this happens.

Doing any sort of to_char() doesn't work as I would want the actual time. I tried to_char() on the group_by Created_DT only it doesn't run.

Any ideas on how I can accomplish this?

Upvotes: 3

Views: 10905

Answers (2)

Brett
Brett

Reputation: 61

I see you've got your answer, but for future reference, I think this would do what you originally asked for (get the first row in a group)

WITH grp AS 
(
    SELECT p.id, p.Created_DT, ROW_NUMBER()
    OVER(PARTITION BY p.id ORDER BY p.Created_DT DESC) AS rnk
      FROM YOUR_TABLE p
)
SELECT g.* FROM grp g WHERE g.rnk = 1

Upvotes: 6

Brett
Brett

Reputation: 8745

I think an aggregate function on created date will do the trick?

select ID, max(Created_DT) from table group by ID

Upvotes: 6

Related Questions