Mysql query 5 cheapest cars, multiple makes, from single table

Question

I have a database of 750 vehicles, I would like to run a query where I select no more than 5 of cheapest vehicles for each make & model and include those in my results.

My table fields are fldYear, fldMake, fldModel, fldRetail etc.

For example I would like the output to include the 5 cheapest Dodge 200 vehicles from 25 in total, the 5 cheapest Honda Accords from 30 in total, the 5 cheapest Jeep Cherokees from 10 in total, and if there only 2 Volkswagon Jettas, to include them as well. In the above example my output would have a total of 17 records.

I have played around with Sorts and Group By but I can't seem to get the desired output.

What have you tried so far? And is it the cheapest make *and* model? So would you get the 5 cheapest VW Golf and 5 cheapest VW Polo, or just the cheapest 5 of VW, regardless of model? — Qirel, Aug 15 '17 at 14:17
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Aug 15 '17 at 14:18
@lauw That doesnt work, because you need limit 5 for each make. — Juan Carlos Oropeza, Aug 15 '17 at 14:23
https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group i think this is what you are looking for — lauw, Aug 15 '17 at 14:26
Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — Caius Jard, Aug 15 '17 at 15:01

Juan Carlos Oropeza · Answer 1 · 2017-08-15T14:28:54.003

1

Just use variables:

SELECT *
FROM (
     SELECT *,
            @row := if(fldMake = @make,
                       @row := @row + 1,
                       if(fldMake := @make, 1, 1)
                      ) as row
     FROM yourCars
     CROSS JOIN ( SELECT @make := '', @row:=0 ) as vars
     ORDER BY fldMake, fldRetail ASC
     ) as T
WHERE T.row <= 5

edited Aug 15 '17 at 14:28

answered Aug 15 '17 at 14:21

Juan Carlos Oropeza

47,252
12
78
118

score 1 · Answer 2 · edited Aug 15 '17 at 17:40

1

I think you should retrieve 5 cheapest vehicle from one brand and put it in array or object. Same for other brands and after retrieving all the required data you can merge them in new array.

edited Aug 15 '17 at 17:40

Jay Blanchard

34,243
16
77
119

answered Aug 15 '17 at 14:22

Anil Shrestha

1,180
11
16

yeah its not the efficient way but its the last option if all of the tried query doesn't works. – Anil Shrestha Aug 15 '17 at 14:26
Thank you for the replies, much appreciated. I had to remove the : in front of the equal signs, otherwise I was getting an error in syntax. The below query returns all of the vehicles. – Frank Lennon Aug 15 '17 at 16:15
If you had shown your query in the first place all of this guessing might not have happened @FrankLennon – Jay Blanchard Aug 15 '17 at 17:40
That's right @FrankLennon because we generally expect those asking questions to provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) Call me a troll one more time and I'll make sure you get an invitation to banned camp. Your comment alone tells us we could've solved your problem had you shown us what it actually was. The second comment you received told you you needed to do that and instead you farted around for 2 hours without a good answer. – Jay Blanchard Aug 15 '17 at 19:02

Mysql query 5 cheapest cars, multiple makes, from single table

2 Answers2