Q:

Returning 5 Most Recent Trips Per ID

I have a table with the number of trips taken and a station_id, and I want to return the 5 most recent trips made per ID (sample image of the table is below)
The query I made below aggregates the station id's and the most recent trip, but I am having a difficult time returning the 5 most recent
SELECT start_station_id, MAX(start_time) 
FROM `bpd.shop.trips` 
group by start_station_id, start_time

Trips:
https://imgur.com/Ebh9FeZ
Any help would be much appreciated, thanks!

A:

You can use row_number():
SELECT t.*
FROM (SELECT t.*,
              ROW_NUMBER() OVER (PARTITION BY start_station_id ORDER BY start_time DESC) as seqnum
      FROM `bpd.shop.trips` t
     ) t
WHERE seqnum <= 5;

