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, 2020 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, 2020 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, 2020 at 13:42
  • Peter Vandivier, I added some sample data
    – Nicholas
    Feb 5, 2020 at 16:33
  • HINT: GROUP BY, SUM()
    – Eric
    Feb 5, 2020 at 17:22

1 Answer 1

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, 2020 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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