CODEWITHSUNDEEP
sql
natc221
natc221

Reputation: 51

GROUP BY one column to find MAX, but keep value from another column - SQL

 A   |   B   |   num
----------------------
123      1       2
123      10      5

Result:

 A   |   B   |   max_num
-------------------------
123      10      5

Let's say the table name is tab, currently I have

SELECT T.A, MAX(T.num) AS max_num
FROM tab T
GROUP BY T.A

However, the result will not contain the column B. 

SELECT T.A, T.B... GROUP BY T.A, T.B

Will also not give the desired result, since max is found based on the A,B pair.

How can I choose the max of num grouped by only A, but then keep the value of B for the max row that is chosen?

Upvotes: 1

Views: 4045

Answers (3)

jarlh
jarlh

Reputation: 44776

Do you mean you want the whole rows where c = the max(c) value for each a? This one will give both rows if it's a tie:

select a, b, c
from t as t1
where c = (select max(c) from t t2
           where t1.a = t2.a)

Upvotes: 0

Dgan
Dgan

Reputation: 10285

1.Select Max num from table

2.Just filter of IN Clause

select * from Mytable where
num in(
select TOP 1 MAX(num)
from mytab
group by colA)

or

For SQL SERVER

You can Use Window function for single Max using ROW_NUMBER ()

select * from (
select ROW_NUMBER () OVER (ORDER BY num desc) rn,*
from tab
)d where d.rn=1

Upvotes: 2

Jens
Jens

Reputation: 69450

This should do the job:

Select t1.A, T1.B,T1.num from tab t1 where (T1.A,T1.num) in (
   SELECT T.A, MAX(T.num) AS max_num
   FROM tab T
   GROUP BY T.A)

Selection the Record where num equals the max(num)

See the SQLFIDDLE

Upvotes: 1

Related Questions