sélectionnez top 1 avec un groupe par

Question

J'ai deux colonnes :

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

Je veux regrouper par code de nom, et retourner uniquement le nom ayant le plus grand nombre d'occurrences. Dans ce cas, le résultat serait

050125  tof
050130  chris
050131  tof

Ceci avec SQL Server 2000

Answer 1

J'utilise habituellement ROW_NUMBER() pour y parvenir. Je ne sais pas comment il se comporte par rapport à différents ensembles de données, mais nous n'avons pas eu de problèmes de performance suite à l'utilisation de l'option ROW_NUMBER .

El PARTITION BY spécifie la valeur par laquelle il faut "grouper" les numéros de ligne, et la clause ORDER BY spécifie comment les enregistrements de chaque "groupe" doivent être triés. Ainsi, vous partitionnez le jeu de données par NameCode, et vous récupérez tous les enregistrements dont le numéro de rangée est égal à 1 (c'est-à-dire le premier enregistrement de chaque partition, ordonné par l'attribut 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

Answer 3

Je n'ai pas essayé mais cela devrait fonctionner,

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 4

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

Answer 5

Voici deux exemples que vous pouvez utiliser. L'utilisation de la table temporaire est plus efficace que la vue, mais elle a été réalisée sur un petit échantillon de données. Vous devriez vérifier vos propres statistiques.

    --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