CODEWITHSUNDEEP
sqloracle-databasegroup-by
Nir
Nir

Reputation: 2629

get first row group by value

I'm missing one piece of the puzzle. I want to select the first row on strid order by c_date and group by strid.

My sql is:

--there should be some 'FIRST' function on strid
select strid over(partition by strid order by c_date) 
from tab
where c_date > :1
  and c_date <= :2

tab

strid    VARCHAR2(255 CHAR)
c_date   TIMESTAMP(6) WITH TIME ZONE

I can use row_number and then select where row_number=1 but i'm trying to avoid this sub-query, and i'm searching for something more optimized..

Upvotes: 1

Views: 77

Answers (1)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186728

Try first_value:

-- do you really want to partition by strid?
select first_value(strid) over (partition by strid order by c_date) 
  from tab
 where (c_date > :1)
   and (c_date <= :2)

Upvotes: 1

Related Questions