sql select first and last record of each group 1

sql select first and last record of each group

select v.*
from (select v.*,
             row_number() over (partition by vehicleid, tripid order by datetime asc) as seqnum_asc,
             row_number() over (partition by vehicleid, tripid order by datetime desc) as seqnum_desc
      from vehicles v
     ) v
where seqnum_asc = 1 or seqnum_desc = 1;

Here is what the above code is Doing:
1. The inner query is assigning a sequence number to each row, based on the vehicleid and tripid.
2. The outer query is selecting only the rows with the lowest and highest sequence numbers.

Similar Posts