CODEWITHSUNDEEP
sqloracle-database
While True
While True

Reputation: 423

Select row with max value saving distinct column

I have values

 - id|type|Status|Comment
 - 1 | P  |  1   | AAA
 - 2 | P  |  2   | BBB
 - 3 | P  |  3   | CCC
 - 4 | S  |  1   | DDD
 - 5 | S  |  2   | EEE

I wan to get values for each type with max status and with comment from the row with max status:

 - id|type|Status|Comment
 - 3 | P  |  3   | CCC
 - 5 | S  |  2   | EEE

All the existing questions on SO do not care about the right correspondence of Max type and value.

Upvotes: 1

Views: 68

Answers (2)

SandPiper
SandPiper

Reputation: 2906

Corrected: The below will use a subquery to figure out each type and what the max status is, then it joins that onto the original table and uses the where clause to only select those rows where the status equals the max status. Of note, if you have multiple records with the same max status, you will get both of them to come up.

WITH T1 AS (SELECT type, MAX(STATUS) AS max_status FROM table_name GROUP BY type)
SELECT t2.id, t2.type, t2.status, t2.comment
FROM T1 LEFT JOIN table_name t2 ON t2.type= T1.type
WHERE t2.status = T1.max_status

Upvotes: 0

Oto Shavadze
Oto Shavadze

Reputation: 42753

This gives you one row per type, which have max status

select * from (
    select  your_table.*, row_number() over(partition by type order by Status desc) as rn from your_table
) tt
where rn = 1

Upvotes: 1

Related Questions