Filter to return just the most recent row for each distinct value in a column

Hi,

I do not know if this is possible but I will try to explain what I am trying to achieve and then if anyone can help that would be really useful.

I have a table which has a list of all trips for a number of vehicles since we started collecting data in 2017. Each vehicle has an ID and for each trip there is a start location and an end location and corresponding times for the start and end of each trip.

What I would like to do is filter this table to show only the last trip for each vehicle.
So the first row is the most recent trip for vehicle ID001, then the next row would be the most recent trip for vehicle ID002 and so on for all of our vehicles.

I can then use this information to show the parked location of each vehicle.

Anyone got any ideas on how to achieve this?

Cheers.

Hi @f-jay100
Unless Custom Expressions allows max/min for other than number column:
https://github.com/metabase/metabase/issues/4482 - upvote by clicking :+1: on the first post
Then you would have to create such query in SQL - you can likely find good examples for the database you’re using on stackoverflow.com

Hi Flamber,

Thanks for the help…

Ok I am looking at a workaround now - I have created a new custom column which changes each datetime for the end of trip to a number in this format … YYYYMMDDHHMM.

So now the highest number in this new column represents the latest trip for each vehicle.

Is there a way now to achieve the filter that I want?

I want a filter which now gives me the row with the maximum of this new column for each distinct value of vehicle ID.

Cheers

@f-jay100
I’m not sure how your structure is, but something like this should work - example with Sample Dataset:

Otherwise you might need to do it in SQL - the max/min in the GUI does not work on dates - and I would recommend that you search stackoverflow.com, since you’ll find much more information specific to your database type and such query isn’t specific to Metabase.

Ah yeah nice one.

I can do this and then start a new question and join these results to the original table and then filter where this new time format column is not empty to get all the information I need for the last trip for each vehicle.

Cheers pal