CODEWITHSUNDEEP
sqlms-accessselectmax
GForce
GForce

Reputation: 31

SQL selecting multiple columns and MAX from one of the columns, only one row to be returned

I need to select multiple columns from a table, together with a max value from one of the columns The result must be only that one row where the max value is in. Here's a simplified version of my table (sorry, don't know how to insert spaces)

╔═════════╦══════════╦═══════════╗
║ CARMAKE ║ CARMODEL ║ CARWEIGHT ║
╠═════════╬══════════╬═══════════╣
║ A       ║ B        ║      1000 ║
║ C       ║ D        ║       900 ║
╚═════════╩══════════╩═══════════╝

The desired result would be

╔═════════╦══════════╦═══════════╗
║ CARMAKE ║ CARMODEL ║ CARWEIGHT ║
╠═════════╬══════════╬═══════════╣
║ A       ║ B        ║      1000 ║
╚═════════╩══════════╩═══════════╝

But when I use 

SELECT carmake, carmodel, MAX(carweight)
FROM   car
GROUP  BY carmake, carmodel

It will show the max of each make etc. instead of only the row with the absolute max. How can I do that?

Upvotes: 1

Views: 2491

Answers (1)

John Woo
John Woo

Reputation: 263723

You can use subquery which gets the maximum value of the carweight table and equate it to the outer query's carweight.

SELECT  *
FROM    tableName
WHERE   carweight = (SELECT MAX(carweight) FROM tableName)

OUTPUT

╔═════════╦══════════╦═══════════╗
║ CARMAKE ║ CARMODEL ║ CARWEIGHT ║
╠═════════╬══════════╬═══════════╣
║ A       ║ B        ║      1000 ║
╚═════════╩══════════╩═══════════╝

The advantage of this is it supports ties, in which they have the same carweight and happens to be the maximum value.

Upvotes: 4

Related Questions