I am trying to figure out how to pull multiple max values from multiple columns. Here is some sample data:
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
I want the top 5 so:
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
Thanks
How about:
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
UNION
You could use UNPIVOT then order by the column value then take only the top 5.
Like This......
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
One thing that you do not tell us is if you want unique results or allow duplicates.
GilM's answer would allow duplicates because he uses UNION ALL.
UNION would return unique results.
