MySQL – How to Select One Item Per Matching ID

greatest-n-per-groupMySQLquery

I have a mysql table(trips) with records about cars. I wish to query the data and return one item per carId. But am unable. Below is the query example getting 50 records, which should also have 50 carId

SELECT * 
FROM `trips` 
WHERE (   carId = 147 
       OR carId = 62 
       OR carId = 59 
       OR carId = 42) 
LIMIT 50

Below is an example that returns 8 records. How to return limit 8 records but one record per car. Example here

Best Answer

SELECT * 
FROM `trips` 
WHERE carId in (147, 62, 59, 42) 
GROUP by carId;

Remove the LIMIT and be sure that you get one row per carId. If you put 20 ids within in ( ), you will get 20 exclusive rows.

But this gives a rough result in the sense that it might choose any one row matching the carId. You might add some condition to pickup any preferred row per carId.

Related Question