1

How to return the id of users that have the maximum of a property in a database?

id A B C

with A B and C being integer?

For instance: if managing a database of users that stole things in a computer game. I want to return users that have stolen the maximum amount of a resource called Metall.

The database sketch is the following:

id Name Adresse Verteidiger Metall Kristal Deuterium Zerstörung Datum

Even if this is false, I tried:

SELECT id I1 FROM `Angriff` WHERE NOT EXISTS 
    SELECT id I2 WHERE Metall > I1.METALL;
0

1 Answer 1

1

To get a single row with a maximum value in MySQL you can simply use LIMIT:

SELECT *
FROM `Angriff` 
ORDER BY Metall DESC
LIMIT 1;

if there are multiple rows with the same maximum this will return only one randomly.

To get all rows with the same maximum you can modify your query to use a Correlated Subquery:

SELECT * FROM `Angriff` AS t1
WHERE NOT EXISTS  -- there's no row with a higher `Metall`
  (  SELECT * FROM `Angriff` AS t2
     WHERE t2.Metall > I1.METALL );

A more common way in Standard SQL:

SELECT * FROM `Angriff`
WHERE Metall = 
  (  SELECT MAX(Metall) FROM `Angriff` );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.