select top 1 with a group by

Question

I have two columns:

namecode name
050125  chris
050125  tof
050125  tof
050130  chris
050131  tof

I want to group by namecode, and return only the name with the most number of occurrences. In this instance, the result would be

050125  tof
050130  chris
050131  tof

This is with SQL Server 2000

Answer 1

I usually use ROW_NUMBER() to achieve this. Not sure how it performs against various data sets, but we haven't had any performance issues as a result of using ROW_NUMBER.

The PARTITION BY clause specifies which value to "group" the row numbers by, and the ORDER BY clause specifies how the records within each "group" should be sorted. So partition the data set by NameCode, and get all records with a Row Number of 1 (that is, the first record in each partition, ordered by the ORDER BY clause).

SELECT 
    i.NameCode, 
    i.Name
FROM 
(
    SELECT 
        RowNumber = ROW_NUMBER() OVER (PARTITION BY t.NameCode ORDER BY t.Name),
        t.NameCode,
        t.Name
    FROM
        MyTable t
) i
WHERE
    i.RowNumber = 1; 

Answer 2

select distinct namecode
, (
     select top 1 name from 
            (
             select namecode, name, count(*) 
             from myTable i
             where i.namecode = o.namecode
             group by namecode, name 
             order by count(*) desc
            ) x
) as name
from myTable o