CODEWITHSUNDEEP
sqlsql-servergroup-by
mmohab
mmohab

Reputation: 2373

SQLSERVER group by (aggregate column based on other column)

I have a table which has 3 columns A, B, C

I want to do a query like this:

select A, Max(B), ( C in the row having max B ) from Table group by A.

is there a way to do such a query?

Test Data:

A    B    C
2    5    3
2    6    1
4    5    1
4    7    9
6    5    0

the expected result would be:

2    6    1
4    7    9
6    5    0

Upvotes: 1

Views: 65

Answers (2)

Azar
Azar

Reputation: 1867

Try this

select t.*
from   table t
join   (Select A,max(b) B from table group by A) c
on  c.a=t.a
and c.b=a.b

Upvotes: 1

Lamak
Lamak

Reputation: 70648

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

Upvotes: 2

Related Questions