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 did not try but this should work,

select top 1 t2.* from (
select namecode, count(*) count from temp 
group by namecode) t1 join temp t2 on t1.namecode = t2.namecode 
order by t1.count desc

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

Answer 3

SELECT max_table.namecode, count_table2.name
FROM
    (SELECT namecode, MAX(count_name) AS max_count
     FROM
         (SELECT namecode, name, COUNT(name) AS count_name
          FROM mytable
          GROUP BY namecode, name) AS count_table1
     GROUP BY namecode) AS max_table
INNER JOIN
    (SELECT namecode, COUNT(name) AS count_name, name
     FROM mytable
     GROUP BY namecode, name) count_table2
ON max_table.namecode = count_table2.namecode AND
   count_table2.count_name = max_table.max_count