CODEWITHSUNDEEP
sqloracle-database
SwiftMango
SwiftMango

Reputation: 15284

How to get the first group from a table?

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?

Upvotes: 0

Views: 30

Answers (1)

DannySlor
DannySlor

Reputation: 4620

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
ITEM ADDRESS date
a x 1
b x 1

Fiddle

Upvotes: 1

Related Questions