Comment retourner une ligne unique basée sur l'agrégation de plusieurs colonnes ?

Question

Désolé pour le titre ambigu, je ne sais pas comment chercher ou poser cette question.

Supposons que nous ayons le tableau A :

RowID        FkId        Rank        Date
ID1          A           1           2013-3-1
ID2          A           2           2013-3-2
ID3          A           2           2013-3-3
ID4          B           3           2013-3-4
ID5          A           1           2013-3-5

J'ai besoin de créer une vue qui renvoie une ligne pour chaque FkId. La ligne doit correspondre au rang maximum et à la date maximum. Ainsi, pour le FkId "A", la requête renverrait la ligne "ID3".

J'ai pu retourner une seule ligne en utilisant des sous-requêtes ; j'ai d'abord obtenu le MAX(Rank), puis j'ai joint une autre requête qui obtient le MAX(Date) group by FkId & Rank.

SELECT TableA.\*
(Select FkId, MAX(Rank) AS Rank FROM TableA GROUP BY FkId) s1 
INNER JOIN (Select FkId, Rank, MAX(Date) AS Date FROM TableA GROUP BY FkId,Rank) s2 ON s1.FkId = s2.FkId AND s1.Rank = s2.Rank 
INNER JOIN TableA ON s2.FkId = TableA.FkId AND s2.Rank = TableA.Rank AND s2.Date = TableA.Date

Existe-t-il une requête plus efficace qui permettrait d'obtenir les mêmes résultats ? Merci de votre attention.

Edit : Ajout de l'ID5 depuis la dernière réponse. Si j'essayais un MAX(rang),MAX(Date) GROUP BY FkId normal, alors pour "A", j'obtiendrais A ; 2 ; 2013-3-5. Ce résultat ne correspondrait pas à un RowId.

Answer 1

Vous pouvez utiliser ROW_NUMBER avec un CTE (en supposant que sql-server >= 2005) :

WITH CTE AS
(
   SELECT TableA.*,
      RN = ROW_NUMBER() OVER (PARTITION BY FkId Order By Rank Desc, Date DESC)
   FROM Table A
)
SELECT RowID,FkId, Rank,Date
FROM CTE WHERE RN = 1

Answer 2

Votre question (clarifiée dans les commentaires de cette réponse) est la suivante :

1. Une seule ligne pour chaque FkId
2. La date maximale et le rang
3. Les résultats doivent correspondre à une ligne des données originales.

Dans le cas où il existe des FkId avec des lignes telles que la date maximale et le rang maximal se trouvent dans des lignes séparées, vous devrez assouplir au moins l'une de ces exigences.

Si vous êtes prêt à assouplir la condition (3), vous pouvez alors utiliser GROUP BY :

SELECT FkId, MAX(Rank) AS Rank, Max(Date) AS Date
FROM TableA
GROUP BY FkId

Compte tenu des informations supplémentaires dans les commentaires. Que vous voulez les dernières entrées les mieux classées pour chaque FkId, ce qui suit devrait fonctionner :

SELECT FkId, Rank, MAX(Date) AS Date
FROM TableA A
WHERE Rank = (SELECT MAX(Rank) 
              FROM TableA sub 
              WHERE A.FkId = sub.FkId 
              GROUP BY sub.FkId)
GROUP BY FkId, Rank

Voici une sqlfiddle pour le montrer en action .

Answer 3

Vous pouvez utiliser Rank() et inline query pour y parvenir.

select * from TableA
where RowID in (
      select rowID from (
           select FKID, RowID, 
                  rank() over (partition by FKID order by [Rank] desc, [Date] desc) as RankNumber
                  from TableA ) A
      where A.RankNumber=1 ) 

Démonstration de SQL Fiddle

Answer 4

Vous pouvez aussi être sournois et faire ce que ljh a suggéré. comme ceci :

select top 1 with ties *
from TableA
order by rank() over (
  partition by FKID
  order by [Rank] desc, [Date] desc
)