CODEWITHSUNDEEP
sqlsql-server-2008
Marty Trenouth
Marty Trenouth

Reputation: 3752

Second maximum and minimum values

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

Upvotes: 5

Views: 16450

Answers (6)

jht
jht

Reputation: 605

let the table name be tblName. select max(number) from tblName where number not in (select max(number) from tblName);

same for min, just replace max with min.

Upvotes: 0

ccis ccis
ccis ccis

Reputation: 11

You could select the next minimum value by using the following method:

SELECT MAX(Number)
FROM
(
  SELECT  top 2 (Number) 
   FROM table1 t1 
   WHERE ID = {MyNumber}
   order by Number
)a

It only works if you can restrict the inner query with a where clause

Upvotes: 1

Farhan
Farhan

Reputation: 2575

This would be a better way. I quickly put this together, but if you can combine the two queries, you will get exactly what you were looking for.

select *
from
(
    select
        myID,
        myNumber,
        row_number() over (order by myID) as myRowNumber
    from MyTable
) x
where x.myRowNumber = 2

select *
from
(
    select
        myID,
        myNumber,
        row_number() over (order by myID desc) as myRowNumber
    from MyTable
) y
where x.myRowNumber = 2

Upvotes: 0

Yuck
Yuck

Reputation: 50835

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.

Upvotes: 1

Andomar
Andomar

Reputation: 238088

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.

Upvotes: 10

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

As I myself learned just today the solution is to use LIMIT. You order the results so that the highest values are on top and limit the result to 2. Then you select that subselect and order it the other way round and only take the first one.

SELECT somefield FROM (
SELECT somefield from table
ORDER BY somefield DESC LIMIT 2) 
ORDER BY somefield ASC LIMIT 1

Upvotes: -1

Related Questions