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

Rick James
  • 78,038
  • 5
  • 47
  • 113
Nicholas
  • 3
  • 3
  • If you need "return one item per carId" then the amount of selected records cannot exceed 4. LIMIT 50 is strange... – Akina Feb 05 '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 05 '20 at 12:10
  • Please see [ask] and provide a [repro] 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. – Peter Vandivier Feb 05 '20 at 13:42
  • Peter Vandivier, I added some sample data – Nicholas Feb 05 '20 at 16:33
  • HINT: GROUP BY, SUM() – Eric Feb 05 '20 at 17:22
  • please check similar question with a great answer - https://dba.stackexchange.com/questions/24327/how-to-select-distinct-for-one-column-and-any-in-another-column – NikitaSerbskiy Feb 05 '20 at 18:00
  • @Nikita - that would mean turning ONLY_FULL_GROUP_BY off - which is not a good idea - like your input though! To the OP, your example on the fiddle (thanks for that!), you have trips of id = 1 and cars of ids 59 and 42 duplicated. Surely, trip_id and car_id together should be a PRIMARY KEY? p.s. welcome to the forum! :-) – Vérace Feb 06 '20 at 06:46
  • Which record per car do you want to return? The first according to some order? – Colin 't Hart Feb 06 '20 at 10:20
  • Look into the ROW_NUMBER() Window Function – John Eisbrener Feb 06 '20 at 15:16
  • Groupwise-max; see the added tag. Though you are not looking for the max of each, you still need the techniques embodied there. – Rick James Feb 09 '20 at 21:26

1 Answers1

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.

Natarajan N Napoleon
  • 269
  • 2
  • 9