CODEWITHSUNDEEP
mysqlgreatest-n-per-group
user256465
user256465

Reputation: 21

Sql query with group by clause

I have fetched given below data, now I want the records with Max(Sum1) group by pid but also mention cname of that max value

pid   cname   sum1

23     Abc     946

23     Xyz     920

18     Lmn     900

18     Pqr     1500

now I want As

23     Abc   946

18     Pqr   1500

Upvotes: 2

Views: 84

Answers (2)

G one
G one

Reputation: 2729

Use this:

select pid, cname, sum1
from Table1
where (pid,sum1) in (select pid,max(sum1) from table1 group by pid)

fiddle

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can do so by using self join with the maxima of sum1 from same table

select t1.pid, t1.cname, t1.sum1
from Table1 t1
join (SELECT MAX(sum1) sum1,pid from Table1 group by pid) t2
on(t1.pid=t2.pid and t1.sum1 =t2.sum1)

Fiddle

Upvotes: 4

Related Questions