I have a table in my database that has rows inserted without updating old ones. That leads to having records with same ID but diffrent timestamps. How to write SQL query that uses window function to read rows with distinct IDs and most recent timestamps?
Asked
Active
Viewed 43 times
-4
-
2Tag your DBMS to the question. You may consider `row_number()` function as a possible solution if your DBMS supports it. – Isolated Aug 28 '23 at 15:13
-
1A regular GROUP BY could perhaps also work? A [mcve] would make things clearer. Also add a tag for the dbms used. – jarlh Aug 28 '23 at 17:02
-
Have you tried anything??? – Eric Aug 28 '23 at 18:28
1 Answers
2
with base as (
select *, row_number() over(partition by id order by timestamp desc) as rn from table
)
select * except(rn) from base
where rn = 1
AlienDeg
- 1,288
- 1
- 13
- 23