mezzoforte006 mezzoforte006 - 1 month ago 5
SQL Question

Selecting Max Count on another Column

I've been stuck on trying to come up with a query to GROUP BY CODE, so that CCC for example should only appear once, and also selecting the Name with the highest count. Can someone point me in the right direction. Thanks

So I want my query to return:

AAA Lee, Albert
BBB Robert, Steven
CCC Jones, Albert
DDD Lim, Kevin
EEE Zhang, Wil


OR

AAA Lee, Albert 12
BBB Robert, Steven 4
CCC Jones, Albert 3
DDD Lim, Kevin 21
EEE Zhang, Wil 11


From Using Sample Data:

CODE NAME Count

AAA Lee, Albert 12
BBB Robert, Steven 4
CCC Robert, Steven 2
CCC Jones, Albert 3
DDD Lim, Kevin 21
EEE Zhang, Wil 11
EEE Wil Zhang 5

Answer

You could LEFT JOIN the table to itself and filter the results

SELECT t1.* FROM Data AS t1
LEFT JOIN Data AS t2
ON
(t1.CODE = t2.CODE) AND (t1.Count < t2.Count)
WHERE t2.Count is null

http://sqlfiddle.com/#!6/cc2ea/4

Comments