Nested SQL Selects? DISTINCT from one column, and get the corresponding Mode for each value in another?

Question

So say I have this table called Key_Values that looks like

--keys--|--values--
  A     |    1     
  A     |    1     
  A     |    2     
  B     |    1     
  B     |    1     
  C     |    3   
  C     |    3     
  C     |    4

I need to write a single select statement that would get all of the distinct keys, and their respective values' mode. It would return this

--keys--|--values--
  A     |    1     
  B     |    1     
  C     |    3   

I'm having some trouble figuring out the correct way to do this. I know I can do a DISTINCT(keys), but I'm not sure how to get the values from that.

Answer 1

You can use window functions if your database supports it:

select key, value as mode
from (select key, value, count(*) as cnt,
             row_number() over (partition by key order by count(*) desc) as seqnum
      from keyvalue
      group by key, value
     ) kv
where seqnum = 1;

In MS Access, this is much more painful:

select key, value, count(*) as cnt
from keyvalue kv
group by key, value
having kv.value = (select top (1) kv2.value
                   from keyvalue kv2
                   where kv2.key = kv.key
                   group by kv2.value
                   order by count(*) desc
                  );