CODEWITHSUNDEEP
sqlsql-order-bytuples
gfppaste
gfppaste

Reputation: 1121

How to select tuples in SQL with the largest specific attibutes

I'm trying to figure out how to list tuples in SQL with the largest specific attributes. For example, say I have table:

Cars(make, model, year, engineSize, pistonCapacity)

and I want to list the tuple(s) with the largest piston capacity.

For example, lets say we have:

Lexus,  SC430  ,  1997,  6,  3.8
Ford ,  Mustang,  1986,  8,  5.0
GMC  ,  Denali ,  2009,  8,  6.5
Ford ,  F350   ,  2009,  8,  6.5

I would want to list the GMC Denali and the Ford F350. I tried this query:

SELECT make, model
FROM Cars
ORDER BY pistonCapacity DESC limit 1

which lists only the GMC Denali.

I then tried this query:

SELECT make, model
FROM Cars
HAVING MAX(pistonCapacity)

but no luck.

Can anyone point me in the right direction?

Thanks for your help in advance!

Upvotes: 0

Views: 1356

Answers (3)

brettw
brettw

Reputation: 11114

SELECT make, model
FROM Cars
WHERE pistonCapacity=(SELECT MAX(pistonCapacity) FROM Cars)

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

SELECT 
      c.make, c.model
FROM 
      Cars AS c
  JOIN
      ( SELECT MAX(pistonCapacity) AS maxPistonCapacity
        FROM Cars
      ) AS cm
    ON
        cm.maxPistonCapacity = m.pistonCapacity

Upvotes: 0

Thilo
Thilo

Reputation: 262764

You did not specify your RDBMS, but in Oracle (and probably most others) you can do

SELECT make, model
FROM Cars
WHERE pistonCapacity = (SELECT max(pistonCapacity) FROM Cars)

Upvotes: 1

Related Questions