Retrieving the row with the biggest value mysql

Question

I've just started my adventure with sql and I am having a problem of retrieving the row with the biggest integer assigned to it (in a different column). I just want to say that I checked the solutions provided on big threads like: SQL select only rows with max value on a column but I just couldn't use it in my case, so I apologise if this will be treated as a duplicate question.

This is the code that I currently have:

SELECT FinalSet.stageName, MAX(Salary) AS 'Biggest Salary'
FROM (
        SELECT FirstSet.stageName, SUM(dailySalary) AS Salary
        FROM (
                SELECT C.stageName, P.dailySalary
                FROM Participant P, Contender C
                WHERE P.contender = C.idContender
        ) FirstSet
        GROUP BY stageName
) FinalSet
GROUP BY stageName;

and that's the output that I've got:

| stageName    | Biggest Salary |
---------------------------------
| DJ Smith     | 200            |
| Echo Beavers | 270            |
| Jazzy Beast  | 220            |
| Pianoman     | 180            |
| Purple bolt  | 240            |
| Summer Hoops | 250            |

When I delete the last line of my code (GROUP BY stageName;) I get this:

| stageName    | Biggest Salary |
---------------------------------
| DJ Smith     | 270            |

Obviously the Biggest Salary is ok but the stageName is not. I just want to also add that it has to be done in one query and I cannot create or change any tables.

This is the Contender table:

CREATE TABLE Contender (
    idContender INTEGER UNIQUE NOT NULL AUTO_INCREMENT,
    coach INTEGER NOT NULL,
    stageName VARCHAR(30) NOT NULL,
    type VARCHAR(10) NOT NULL,
    PRIMARY KEY (idContender),

    FOREIGN KEY (coach)
            REFERENCES Coach(idCoach)
);

And this is the Participant table:

CREATE TABLE Participant (
    idParticipant INTEGER UNIQUE NOT NULL AUTO_INCREMENT,
    contender INTEGER NOT NULL,
    name VARCHAR(20) NOT NULL,
    surname VARCHAR(30) NOT NULL,
    DoB DATE,
    phone VARCHAR(20),
    dailySalary INTEGER,
    gender VARCHAR(10),
    PRIMARY KEY (idParticipant),

    FOREIGN KEY (contender)
            REFERENCES Contender(idContender)
);

You should post your data model to make it easier to help you: Your tables, their columns and the logical relationship between them. — rf1234, Mar 23 '20 at 19:58

score 1 · Accepted Answer · answered Mar 23 '20 at 20:23

1

Do you need below -

SELECT C.stageName, SUM(P.dailySalary) AS Salary
  FROM Participant P, Contender C
 WHERE P.contender = C.idContender
 GROUP BY stageName
 ORDER BY Salary DESC
 LIMIT 1;

answered Mar 23 '20 at 20:23

Ankit Bajpai

13,128
4
25
40

Thank you very much! – Jan Marczak Mar 23 '20 at 20:27

Retrieving the row with the biggest value mysql

1 Answers1