if object_id ('temp') is not null
drop table temp
go
create table temp
(id varchar(20)
, time time
, product varchar(100)
)
go
insert into temp (id, time, product)
select id, time, product
from
(
values
('A', '08:10', 'pizza' )
, ('A', '09:55', 'noodle' )
, ('B', '12:30', 'pizza' )
) a (id, time, product)
go
-- Consider the pros and cons of both queries.
-- 1
; with a
as
(
select row_number() over (partition by id order by time asc) rn , *
from temp
)
select *
from a
where rn = 1
-- 2
select b.*
from
(
select distinct id
from temp a
) a
cross apply
(
select top 1 *
from temp
where id = a.id
order by time
) b
