MMousa MMousa - 3 months ago 11
SQL Question

how to select first row in table

i am trying to select first occurrence of the "Quality" column per each Type (A, B, C, ...etc)

infotable
--------
time Type quality
1:00 A 1
1:05 A 1
1:10 A 2
1:13 A 2
1:17 A 3
1:20 B 3
1:22 B 2
1:25 B 2
1:30 B 1

and i want the final result to be like this:
--------------------------
time Type quality
1:00 A 1
1:10 A 2
1:17 A 3
1:20 B 3
1:22 B 2
1:30 B 1


i tried to do it through multiple CTE but failed to get the correct output

with
cte_type
as (
select *,
row_number() over (partition by type order by time asc ) as rn_type
from infotable
),
cte_quality
as (
select *,
row_number() over (partition by quality order by time asc ) as rn_quality
from cte_type
)
select * from cte_quality
where rn_quality = 1;


Any thoughts on how i could get the required result would be really appreciated

Answer

You only need a single cte/row_number:

with 
  cte
as (
  select *,
     row_number()
     over (partition by type, quality 
           order by time asc ) as rn
  from infotable
  )
select * from cte
where rn = 1;