CODEWITHSUNDEEP
sql
Calaydo Minds
Calaydo Minds

Reputation: 5

distinct name with each their max value sorted by their own max value with a condition in sql

I currently have the following table:

    Name    |  key   |  State
    User 1  |  12    |  15
    User 2  |  12    |  100
    User 3  |  144   |  35
    User 1  |  66    |  21
    User 1  |  72    |  25
    User 3  |  12    |  22
    User 2  |  12    |  50

i want to find user then get its maximum state number and sort the maximum state from highest value where it has a column key=12.

the rexpected result is this.

    Name    |  key   |  State
    User 2  |  12    |  100
    User 3  |  12    |  22
    User 1  |  12    |  15

i cant solve the problem myself that is why i need help, sorry for that though

Upvotes: 0

Views: 39

Answers (3)

nish
nish

Reputation: 1221

Here is the solution.

SELECT Name, key, MAX(state) AS state
FROM <tablename>
WHERE key = 12
GROUP BY Name, key
ORDER BY state Desc;

Upvotes: 2

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

SELECT Name, key, MAX(State) as State
FROM <tabe>
WHERE 
 key = 12
GROUP BY Name, key
ORDER BY State DESC

Upvotes: 0

Valerica
Valerica

Reputation: 1630

Here is a solution:

SELECT
    Name
    ,Key
    ,MAX(State) AS State
FROM your_table
WHERE Key = 12
GROUP BY Name, Key
ORDER BY State DESC

Upvotes: 0

Related Questions