CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
WorksOnMyLocal
WorksOnMyLocal

Reputation: 1689

Get a max record for each unique column value in a table

I have a database table like this

A           ||        B      ||         C
------------------------------------------
1                    ABC               10
1                    XYZ                5
2                    EFG               100
2                    LMN               150
2                    WER                50
3                    ABC                50
3                    XYZ                75

Now i want to have a result set like this,where i want to have the max value of column C for each value in column A

A           ||        B      ||         C
-----------------------------------------
1                    ABC                10
2                    LMN               150
3                    XYZ                75

I have tried using distinct and max() but it did not work. like this

select distinct #table.A,#table.B,MAX(#table.C) from #table group by #table.A,#table.B

Is there a simple way to achieve this?

Upvotes: 5

Views: 3438

Answers (4)

Vamshi Krishna Rao N
Vamshi Krishna Rao N

Reputation: 101

If you order by both C and B the combination of both may or may not give you the highest value of Column C. So I feel the below query should work for your specific requirement.

SELECT table.A, table.B, table.C
    FROM
    (
        SELECT A, B, C, ROW_NUMBER() OVER (PARTITION BY A ORDER BY C DESC) row_num
        FROM yourTable
    ) table
    WHERE table.row_num = 1

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521299

Using MAX() as a window function:

SELECT t.A, t.B, t.C
FROM
(
    SELECT A, B, C, MAX(C) OVER (PARTITION BY A) max_C
    FROM yourTable
) t
WHERE t.C = t.max_C

If you want to retrieve only a single max record for each group of A values, then you should use the method suggested by @GurV, which is the row number:

SELECT t.A, t.B, t.C
FROM
(
    SELECT A, B, C, ROW_NUMBER() OVER (PARTITION BY A ORDER BY C, B DESC) row_num
    FROM yourTable
) t
WHERE t.row_num = 1

Note carefully the ORDER BY C, B inside the call to ROW_NUMBER(). This will place max C records at the top of each partition, and will then also order descending by B values. Only one value will be retained though.

Upvotes: 3

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Just an another way with a simple Join and Group BY

Schema: 

SELECT * INTO #TAB1 FROM (
SELECT 1 A, 'ABC' B , 10 C
UNION ALL
SELECT 1 , 'XYZ' , 5
UNION ALL
SELECT 2 , 'EFG' , 100
UNION ALL
SELECT 2 , 'LMN' , 150
UNION ALL
SELECT 2 , 'WER' , 50
UNION ALL
SELECT 3 , 'ABC' , 50
UNION ALL
SELECT 3 , 'XYZ' , 75
)A

Do join to sub query

SELECT C2.A,C1.B, C2.MC
FROM #TAB1 C1
INNER JOIN 
(
SELECT A, MAX(C) MC 
FROM #TAB1
GROUP BY A
)AS C2 ON C1.A=C2.A AND C1.C= C2.MC

And the result will be

+---+-----+-----+
| A |  B  | MC  |
+---+-----+-----+
| 1 | ABC |  10 |
| 2 | LMN | 150 |
| 3 | XYZ |  75 |
+---+-----+-----+

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can use window function to do this:

select * from (select
    t.*,
    row_number() over (partition by A order by C desc) rn
from your_table t) t where rn = 1;

If those aren't supported, use JOIN:

select t1.*
from your_table t1
inner join (
    select A, max(C) C
    from your_table
    group by A
) t2 on t1.A = t2.A
and t1.C = t2.C;

Upvotes: 0

Related Questions