SQL: getting the full record with the highest counter

Question

I am trying to write sql that gives 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 to record a complete record with the highest score. I tried to make a group, but if I group Opt1, Opt2, Opt3, this does not work, because it returns the highest score for each combination (ID Num, Opt2, Opt3, Opt4), which is not what I want. If I only group by ID Num, I can get max for each ID Num, but I'm losing information about which combination (Opt1, Opt2, Opt3) gives this score.

It seems to me that I have done this before, but I do not often work with sql, and I do not remember how to do it. Is there an easy way to do this?

Answer 1

Edit , , . , , .

---

, 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

, id , ...

Answer 2

- :

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

, idnum idnums

Answer 3

Try this query:

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

It should work on Access, but I have not tested it.