You need to use one of the
ranking functions[
^].
For example, you can use
ROW_NUMBER()
. Try this:
DECLARE @myTable TABLE (aValue INT)
INSERT INTO @myTable (aValue)
VALUES(123)
INSERT INTO @myTable (aValue)
VALUES(456)
INSERT INTO @myTable (aValue)
VALUES(789)
INSERT INTO @myTable (aValue)
VALUES(234)
INSERT INTO @myTable (aValue)
VALUES(567)
INSERT INTO @myTable (aValue)
VALUES(345)
INSERT INTO @myTable (aValue)
VALUES(678)
INSERT INTO @myTable (aValue)
VALUES(999)
SELECT ROW_NUMBER() OVER(ORDER BY aValue DESC) AS aPosition, aValue
FROM @myTable
Results:
1 999
2 789
3 678
4 567
5 456
6 345
7 234
8 123
SELECT aValue As [ThirdHighestValue]
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY aValue DESC) AS aPosition, aValue
FROM @myTable
) AS t1
WHERE t1.aPosition = 3
Result: 678