Select one item per matching ids(value)

Question

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

score 0 · Accepted Answer · answered Feb 07 '20 at 15:43

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.

Select one item per matching ids(value)

1 Answers1