I have a itemlocation table like:

item     address  date
a        x        1
b        x        1
a        y        2
b        y        2
...

I want to return the result set of

item     address  date
a        x        1
b        x        1

where it includes all the items at the first address group order by date.

I was looking at something similar to rownum but it's for the first in the group, not the first group of a table. Something like:

select * from (select * from itemlocation group by address order by date) where group_number <= 1;

How can I achieve this?

CodePudding user response：

We use rank() to find all the rows with the earliest date and then filter by rnk_dte = 1

select  item
       ,address
       ,"date"
from   (
       select  t.*
              ,rank() over(order by "date") as rnk_dte
       from   t
       ) t
where  rnk_dte = 1

Fiddle