Second maximum and minimum values

Question

Given a table with multiple rows of an int field and the same identifier, is it possible to return the 2nd maximum and 2nd minimum value from the table.

A table consists of

ID      |   number
------------------------
1       |     10
1       |     11
1       |     13
1       |     14
1       |     15
1       |     16

Final Result would be

ID      |   nMin    |   nMax
--------------------------------
1       |     11    |    15

Answer 1

You can use row_number to assign a ranking per ID. Then you can group by id and pick the rows with the ranking you're after. The following example picks the second lowest and third highest :

select  id
,       max(case when rnAsc = 2 then number end) as SecondLowest
,       max(case when rnDesc = 3 then number end) as ThirdHighest
from    (
        select  ID
        ,       row_number() over (partition by ID order by number) as rnAsc
        ,       row_number() over (partition by ID order by number desc) as rnDesc
        ) as SubQueryAlias
group by
        id

The max is just to pick out the one non-null value; you can replace it with min or even avg and it would not affect the outcome.

Answer 2

This will work, but see caveats:

SELECT Id, number
INTO #T
FROM (
  SELECT 1 ID, 10 number
  UNION
  SELECT 1 ID, 10 number
  UNION
  SELECT 1 ID, 11 number
  UNION
  SELECT 1 ID, 13 number
  UNION
  SELECT 1 ID, 14 number
  UNION
  SELECT 1 ID, 15 number
  UNION
  SELECT 1 ID, 16 number
) U;

WITH EX AS (
  SELECT Id, MIN(number) MinNumber, MAX(number) MaxNumber
  FROM #T
  GROUP BY Id
)
SELECT #T.Id, MIN(number) nMin, MAX(number) nMax
FROM #T INNER JOIN
     EX ON #T.Id = EX.Id
WHERE #T.number <> MinNumber AND #T.number <> MaxNumber
GROUP BY #T.Id

DROP TABLE #T;

If you have two MAX values that are the same value, this will not pick them up. So depending on how your data is presented you could be losing the proper result.