0

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

10
  • If you need "return one item per carId" then the amount of selected records cannot exceed 4. LIMIT 50 is strange...
    – Akina
    Feb 5 '20 at 12:08
  • Specify MySQL version. Specify your SQL mode. Provide the criteria which allows to select one specific record from a lot of records with the same carId.
    – Akina
    Feb 5 '20 at 12:10
  • Please see How to Ask and provide a minimal reproducible example with the table definitions and sample data to better define the question. Start here and edit as necessary until you can provide the actual output you are seeing locally. Feb 5 '20 at 13:42
  • Peter Vandivier, I added some sample data
    – Nicholas
    Feb 5 '20 at 16:33
  • HINT: GROUP BY, SUM()
    – Eric
    Feb 5 '20 at 17:22
0
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.

1
  • The OR is turned into IN in this example.
    – Rick James
    Feb 9 '20 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.