CODEWITHSUNDEEP
mysqlsubquerymaxhaving
Dale En Dol
Dale En Dol

Reputation: 13

mysql subquery and having max value of subquery

SELECT plaka, party_id, sum(inf) i 
  FROM party_sehir
 GROUP BY party_id, plaka

This is my sub-query and the results are:

plaka   party_id   i 
34      1          42
34      2          9
35      1          11
35      2          26

from these results I want to get maximum i of every plaka and rows party_id, like this:

plaka  party_id    i 
34     1           42
35     2           26

I mean the maximum for each plaka. I also tried:

SELECT * 
  FROM ( SELECT plaka, party_id, sum(inf) i 
           FROM party_sehir
          GROUP BY party_id, plaka ) t1 
 GROUP BY plaka  
HAVING i >= max(i)

It didn't work; is anyone able to help?

thank you

Upvotes: 1

Views: 7551

Answers (2)

Er. Anurag Jain
Er. Anurag Jain

Reputation: 1793

Please try group by for your solution

SELECT plaka ,party_id   , max( `i` ) FROM `party_sehir` GROUP BY `plaka`

OR 

SELECT * FROM `party_sehir` AS s1 WHERE s1.i = (SELECT max( `i` ) FROM party_sehir AS  
   s2  WHERE s1.plaka = s2.plaka ) GROUP BY `plaka`

Upvotes: 0

hkutluay
hkutluay

Reputation: 6944

It needs a longer query, like

select plaka,party_id, i 
from
(SELECT plaka,party_id, sum(inf) i FROM party_sehir
 GROUP BY  party_id, plaka) sum_group
where (i,plaka) in (
SELECT max(i), plaka
 FROM (SELECT plaka,party_id, sum(inf) i FROM party_sehir
 GROUP BY  party_id, plaka) t1 group by plaka )

Upvotes: 2

Related Questions