CODEWITHSUNDEEP
sqlhive
MMousa
MMousa

Reputation: 5

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

Upvotes: 0

Views: 1778

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146607

no need for cte

Select Time, Type, Quality
From infotable it
Where time =
   (Select Min(time)
    From infotable
    Where type = it.Type
       and Quality = it.Quality)

This will also show you if there is more than one row for a type & Quality at the same time.

Upvotes: 0

debus
debus

Reputation: 640

I think it should be something like 

SELECT type, quality, min(time)
FROM infotable
GROUP BY type,quality

Upvotes: 2

dnoeth
dnoeth

Reputation: 60502

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;

Upvotes: 0

Related Questions