Requête SQL pour obtenir plusieurs valeurs maximales à partir de plusieurs colonnes

Question

J'essaie de comprendre comment extraire plusieurs valeurs maximales de plusieurs colonnes. Voici quelques exemples de données :

DATE | A | B | C

4/4/2011 | 64.4 | 62.1 | 33.3

4/5/2011 | 34.6 | 33.5 | 32.3

4/6/2011 | 33.1 | 49.4 | 32.1

4/7/2011 | 55.2 | 32.8 | 33.5

4/8/2011 | 31.2 | 50.1 | 30.4

4/9/2011 | 31.7 | 31.1 | 30.4

Je veux le top 5 donc :

4/4/2011 | 64.4

4/4/2011 | 62.1

4/7/2011 | 55.2

4/8/2011 | 50.1

4/6/2011 | 49.4

Merci

Answer 1

Pourquoi pas :

SELECT TOP 5 Date, Val 
FROM (SELECT Date, A as Val FROM T
      UNION ALL
      SELECT Date, B FROM T
      UNION ALL
      SELECT DATE, C FROM T
) AS x
ORDER BY x.Val DESC

Answer 2

Vous pourriez utiliser UNPIVOT puis ordonner par la valeur de la colonne et ne prendre que les 5 premiers.

Comme ça......

CREATE TABLE #Data 
(
    [Date] DATE,
    A  FLOAT,
    B  FLOAT,
    C  FLOAT
)

INSERT INTO 
    #Data
SELECT '4/4/2011' AS Date, '64.4' AS A, '62.1' AS B, '33.3' AS C
UNION SELECT '4/5/2011' AS Date, '34.6' AS A, '33.5' AS B, '32.3' AS C
UNION SELECT '4/6/2011' AS Date, '33.1' AS A, '49.4' AS B, '32.1' AS C
UNION SELECT '4/7/2011' AS Date, '55.2' AS A, '32.8' AS B, '33.5' AS C
UNION SELECT '4/8/2011' AS Date, '31.2' AS A, '50.1' AS B, '30.4' AS C
UNION SELECT '4/9/2011' AS Date, '31.7' AS A, '31.1' AS B, '30.4' AS C

SELECT * FROM #Data

SELECT TOP 5
    [Date],
    [Type],
    [Value]
FROM
(
    SELECT  
        [Date],
        [A],
        [B],
        [C]
    FROM
        #Data 
)pvt
UNPIVOT
(
    [Value] FOR [Type] IN
    ( [A],[B],[C])
)AS unpvt
ORDER BY
    [Value] DESC

DROP TABLE #Data

Answer 3

Une chose que vous ne nous dites pas est si vous voulez des résultats uniques ou si vous autorisez les doublons.

La réponse de GilM permettrait des doublons car il utilise UNION ALL.

UNION donnerait des résultats uniques.