Johnny Johnny - 3 months ago 10
SQL Question

sql query that partitions the data and orders by time and then returns only specific records within a partition

So what I mean exactly is: data is partitioned by name and ordered by date

data is partitioned by name and ordered by date

I would like now to select only those rows in each partition which are coming after the rowNo 3 (where no is null and genre is null)

So result of the query should return rowNo 4 and 5

Query used:

select
name, no, genre, date,
ROW_NUMBER() OVER(PARTITION BY name, genre ORDER BY date)
from
sourceTable

vkp vkp
Answer

Assuming there is only one row per name where no and genre are null, you can use

select t1.* 
from tablename t1
join tablename t2 on t1.name = t2.name and t2.no is null and t2.genre is null
where t1.date > t2.date
Comments