SQL: Getting the full record with the highest count.

Question

I'm trying to write sql that produces the desired result from the data below.

data:

IDNum  Opt1 Opt2 Opt3 Count
1      A     A    E    1 
1      A     B    J    4
2      A     A    E    9
3      B     A    F    1
3      B     C    K    14
4      A     A    M    3
5      B     D    G    5
6      C     C    E    13
6      C     C    M    1

desired result:

IDNum  Opt1 Opt2 Opt3 Count
1      A     B    J     4
2      A     A    E     9
3      B     C    K     14
4      A     A    M     3
5      B     D    G     5
6      C     C    E     13

Essentially I want, for each ID Num, the full record with the highest count. I tried doing a group by, but if I group by Opt1, Opt2, Opt3, this doesn't work because it returns the highest count for each (ID Num, Opt2, Opt3, Opt4) combination which is not what I want. If I only group by ID Num, I can get the max for each ID Num but I lose the information as to which (Opt1, Opt2, Opt3) combination gives this count.

I feel like I've done this before, but I don't often work with sql and I can't remember how. Is there an easy way to do this?

Answer 1

Edit Prior to op clarifying question for access this would have worked. I am not famillar with access to know if this query would be supported.

---

I think this will work on SQL Server.

select * from data
inner join (select idnum, max(count) from data
            group by idNum )sub
on sub.IdNum=data.IdNum && sub.Count=data.Count

Of course if you have two id's with the same count it would return both rows...

Answer 2

Something like this:

SELECT * FROM table AS t1
JOIN ( SELECT id, max(count) as Id FROM table GROUP BY id ) AS t2
ON t1.id = t2.id AND t1.id = t2.id

This assumes that no idnum has the same max count or you'll get two idnums

Answer 3

Try this query:

SELECT * FROM my_table
GROUP BY IDNum
HAVING Count = MAX(Count)

It should work on Access, but I didn't test it.