Oracle SQL Question about grouping and getting latest record

user_10962698 0
Viewed 28

Hi suppose i have the following dataset:

first, last, height, phone, email,date
Test, Test1, ,,,1/1/2001
Test, Test1, ,,,1/1/2004
Test2, Test2,5 4,,,1/1/2007
Test2, Test2,5 4 ,,,1/1/2010
Test2, Test2,5 4 ,,,1/1/2003
Test3, Test3,,123456789,,1/1/2020
Test3, Test3,,123456789,,1/1/2001
Test4, Test4,,,test@test.com,1/1/2013
Test4, Test4,,,test@test.com,1/1/2014
Test4, Test4,,,test@test.com,1/1/2018

I know i can get the count by doing having count(first) > X but my goal is to get the newest record so the final result should be:

first, last, height, phone, email,date
Test, Test1, ,,,1/1/2004
Test2, Test2,5 4 ,,,1/1/2010
Test3, Test3,,123456789,,1/1/2020
Test4, Test4,,,test@test.com,1/1/2018

I'm having a hard time figuring out a query to get the wanted results.

Thanks,

oracle sql
2 Answers

You could use ROW_NUMBER

SELECT first, last, height, phone, email,date from (
    SELECT 
        first, last, height, phone, email,date,ROW_NUMBER() over (partition by first order by date desc) as num
    from YOUR_TABLE
) A where num = 1
user_127036380

You can try to use MAX with GROUP BY from your sample data

SELECT first, last, height, phone, email,MAX(date)
FROM T
GROUP BY first, last, height, phone, email

or you can try to use fetch first

select *
from T
order by row_number() over (partition by first, last, height, phone, email order by date desc)
fetch first 1 row with ties;
user_51760710
Related