run select on multiple subgroups

Question

i have a table with say
Name, ForeignKey, datestart, dateend

I want to select the most recent item that has not ended yet for each Foreignkey. for a date 2020.07.02 So if the table is:

name |FK| datestart |dateend
a    | 1| 2020.01.01| 2020.06.01  
b    | 1| 2020.02.01| 2020.07.01  
c    | 1| 2020.02.15| 2020.08.01  
d    | 1| 2020.02.01| 2020.09.01  
e    | 2| 2020.01.01| 2020.06.01  
f    | 2| 2020.02.01| 2020.08.01  
g    | 2| 2020.06.01| 2020.08.01  
h    | 2| 2020.02.01| 2020.09.01  

i want the result

name |FK| datestart |dateend
c    | 1| 2020.02.15| 2020.08.01  
g    | 2| 2020.06.01| 2020.08.01  

can i do this in one statement?

i can use

select name from table
where datestart <= 'date'
and datestart = (
Select max( datestart ) FROM table
where 'date' >= dateend
AND ( dateend is null or 'date' <= dateend))

name |FK| datestart |dateend
g    | 2| 2020.06.01| 2020.08.01  

to select the most recent viable result. but i need to do it once for each group.

Answer 1

You can use analytical function as follows:

select * from
(select t.*, 
        row_number() over (partition by ForeignKey order by datestart desc) as rn 
   from table t
  where datestart <= 'date') t
where rn = 1