Create new table based on max count per id

Question

I have a table that looks like this:

ID  Code    Count
A     AA    10
A     BB    7
B     AA    7
B     BB    10
C     CC    10
C     DD    7

I was wondering how I could select each id with the highest count?

I tried using this code as a reference, but am having no luck: Can I do a max(count(*)) in SQL?

Tag your question with the database you are using. – Gordon Linoff Oct 19 '17 at 02:37 — Gordon Linoff, Oct 19 '17 at 02:37

score 0 · Accepted Answer · answered Oct 18 '17 at 20:14

0

There are lots of ways. Here is one:

select t.*
from t
where t.count = (select max(t2.count) from t t2 where t2.id = t.id);

answered Oct 18 '17 at 20:14

Gordon Linoff

I receive this error `cannot recognize input near 'select' 'max' '(' in expression specification (state=42000,code=40000)` – Qwerty Oct 18 '17 at 20:33

score 0 · Answer 2 · answered Oct 18 '17 at 21:25

0

You can do max(count(*)) via splitting into 2 levels:

select id, max(cnt) as max_cnt
from (select id, count(*) as cnt from table group by id) as a
group by id

answered Oct 18 '17 at 21:25

Alex

2 Answers2