SQL: Getting the full record with the highest count.
- by sqlnoob
I'm trying to write sql that produces the desired result from the data below.
data:
ID Num 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:
ID Num 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?
