0

I've got a table with the schema

Cars(make, model, country, horsepower, doors)

I need to determine which cars in the table have the greatest horsepower (ties are allowed).

My first thought was something like

SELECT model
FROM Cars
WHERE horsepower = MAX(horsepower)

but of course that resulted in a grouping error.

I tried doing various increasingly complicated things with ORDER and HAVING that I no longer remember, but had no luck.

Eventually, I solved it by using a subquery:

SELECT model
FROM Cars
WHERE horsepower IN
    (SELECT MAX(horsepower) FROM Cars)

but for some reason I can't shake the feeling that this is hacky and that there's a better way to do it (without resorting to a subquery). Suggestions?

For those who care about such things, yes, this is (or, more accurately, was) homework. But I already got the points with my subquery answer, if that makes a difference. At this point, I just want to find out if there was a better way, and if so, what.

One other thing, I doubt it matters for something this simple, but my teacher is using MySQL.

SOLO
  • 868
  • 9
  • 19
  • 1
    `SELECT model FROM Cars ORDER BY horsepower DESC LIMIT 1`??? – Niet the Dark Absol Jan 29 '18 at 20:36
  • @NiettheDarkAbsol what if you have multiple cars with the max horsepower? – Shadow Jan 29 '18 at 20:39
  • @SOLO check out the self-join solution in the accepted answer. You only need to join on the horsepower. – Shadow Jan 29 '18 at 20:40
  • @Shadow that self join one sounds interesting, but I would worry about the performance; since that kind of WHERE condition must be evaluated after the join, I'd expect roughly n-squared comparisons to be required. – Uueerdo Jan 29 '18 at 20:49
  • @Uueerdo obviously you need to test the performsnce, but that's how you do it without a subquery and that was the question. – Shadow Jan 29 '18 at 20:52
  • The second query here in the question is perfect for this task. I would just change `IN` to `=`. – Paul Spiegel Jan 29 '18 at 20:54
  • @Shadow I almost didn't understand what you were pointing me to, but yeah, that seems to be what I wanted. Got the same result with `SELECT a.model FROM Cars a LEFT OUTER JOIN Cars b ON a.horsepower < b.horsepower WHERE b.model IS NULL`. Thanks! – SOLO Jan 30 '18 at 03:18

0 Answers0