CODEWITHSUNDEEP
sqlsql-server
Hamish
Hamish

Reputation: 629

Filter table using distinct column values and greatest value from another column

Given the following data, I want to unique values for columns A but I only want the record with the largest value of column B.

id |  A  | B
-------------    
1  | 'w' | 1
2  | 'x' | 0
3  | 'y' | 0
4  | 'z' | 0
5  | 'w' | 2
6  | 'w' | 1
7  | 'w' | 0

So the expected result of such a query would be this:

id |  A  | B
-------------    
2  | 'x' | 0
3  | 'y' | 0
4  | 'z' | 0
5  | 'w' | 2

Any ideas?

Upvotes: 1

Views: 254

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use ROW_NUMBER:

WITH CTE AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC)
    FROM YourTable
)
SELECT
    id, A, B
FROM CTE
WHERE RN = 1
ORDER BY ID

SQL FIDDLE

Upvotes: 2

void
void

Reputation: 7890

in fact you need max(B) group by A, I name this as INNER GROUPING BY, you can achieve such this grouping by using a specific self join:

select t1.id,t1.A,t1.B 
from table_name t1
     join (select A,max(B)B
           from table_name 
           group by A)t2
     on t1.A=t2.A and t1.B=t2.B
order by t1.id

Result:

id |  A  | B
-------------    
2  | 'x' | 0
3  | 'y' | 0
4  | 'z' | 0
5  | 'w' | 2

THE DEMO

Upvotes: 2

Related Questions