TSQL mode (as in mean, median,mode)

Question

I'm trying to calculate the mode of a series of idsofInterest in a table, each with an accompanying valueOfInterest such:

idsOfInterest | valueOfInterest  
2             | 1A  
2             | 1A  
2             | 3B  
1             | 2A  
1             | 2C  
1             | 2A  
4             | 3B  
4             | 3B  
4             | 4C  

but with millions of rows.
Each list of idOfInterest is sufficiently long that multimodes are not a problem. Ideally, I would like something like

idsOfInterest | modeValueOfInterest  
1             | 2A  
2             | 1A  
3             | 3C  
4             | 3B

Answer 1

The mode is the most common value. You can get this with aggregation and row_number():

select idsOfInterest, valueOfInterest
from (select idsOfInterest, valueOfInterest, count(*) as cnt,
             row_number() over (partition by idsOfInterest order by count(*) desc) as seqnum
      from table t
      group by idsOfInterest, valueOfInterest
     ) t
where seqnum = 1;