select top 1 with a group by

I have two columns:

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

I want to group by namec开发者_开发知识库ode, 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

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; 

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 

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

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

Here are to examples that you could use but the temp table use is more efficient than the view, but was done on a small data sample. You would want to check your own statistics.

    --Creating A View
GO
CREATE VIEW StateStoreSales AS
SELECT t.state,t.stor_id,t.stor_name,SUM(s.qty) 'TotalSales'
,ROW_NUMBER() OVER (PARTITION BY t.state ORDER BY SUM(s.qty) DESC) AS 'Rank'
FROM [dbo].[sales] s
JOIN [dbo].[stores] t ON (s.stor_id = t.stor_id)
GROUP BY t.state,t.stor_id,t.stor_name

GO
SELECT * FROM StateStoreSales
WHERE Rank <= 1
ORDER BY TotalSales Desc

DROP VIEW StateStoreSales


---Using a Temp Table
SELECT t.state,t.stor_id,t.stor_name,SUM(s.qty) 'TotalSales'
,ROW_NUMBER() OVER (PARTITION BY t.state ORDER BY SUM(s.qty) DESC) AS 'Rank'     INTO #TEMP
FROM [dbo].[sales] s
JOIN [dbo].[stores] t ON (s.stor_id = t.stor_id)
GROUP BY t.state,t.stor_id,t.stor_name

SELECT * FROM #TEMP
WHERE Rank <= 1
ORDER BY TotalSales Desc

DROP TABLE #TEMP