5

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
Yuck
  • 49,664
  • 13
  • 105
  • 135
Marty Trenouth
  • 3,712
  • 6
  • 34
  • 43

6 Answers6

10

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • +1 I can't believe only 1 in 5 answers actually managed this... and allowed for different IDs too. – gbn Dec 08 '11 at 20:23
  • to get the second higest why not just `select X.* from (select row_number() over partition by id order by number) as rn, id, number) X where rn = 2`? (And then to get the 2nd lowest, add "desc" to order by? – Levin Magruder Dec 08 '11 at 20:26
  • @MakeMinePanacea: The OP is asking for both in one row, so the answer has to "pivot" two specific rows into two columns – Andomar Dec 08 '11 at 20:29
  • This doesn't give the right results. It does `1|11|14` instead of `1|11|15`. Should be a simple edit, though (`rnDesc = 2`). – Yuck Dec 08 '11 at 20:30
  • @Yuck: Yeah, I just changed 2nd highest to 3rd highest to make the answer clearer (hopefully) – Andomar Dec 08 '11 at 20:31
  • 1
    Worked well. I'm sure theres gonna ba tweaks here and there (I'm actually doing it on a couple columns. Also using Dense_Rank rather than rowNumber so that I can account for multiple entries. Thanks a bunch! – Marty Trenouth Dec 08 '11 at 20:40
  • +1 good answer. Specially when you add @Marty's comment (Dense_Rank) :) – eglasius Mar 13 '13 at 14:25
1

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

ccis ccis
  • 11
  • 2
1

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.

Yuck
  • 49,664
  • 13
  • 105
  • 135
  • @gbn: One query to find the MIN/MAX, the second to *not* use them :) The crap at the top is just setup so it can be run via copy & paste, but I'm sure you're aware of that. – Yuck Dec 08 '11 at 20:23
  • My intention was to to highlight that self joins and aggregates are overkill compared to use of ROW_NUMBER (or DENSE_RANK for joint 2nd maximum etc) does it far more elegantly. – gbn Dec 08 '11 at 20:26
  • 1
    @gbn: Just FYI, actual execution plan shows that my example is better optimized than using `ROW_NUMBER()` using the answer provided by @Andomar. – Yuck Dec 08 '11 at 20:34
  • On SQL Server 2005 or SQL Server 2008? Aggregates tend to be better on '2005, window functions improve with later versions – gbn Dec 08 '11 at 20:36
  • @gbn: I'm using 2K8 R2. If you want to head to chat I can paste in a screen shot so you can see what I mean. Run together in batch the split is 37% vs 63% in favor of aggregates. – Yuck Dec 08 '11 at 20:37
  • I believe you. What about IO? – gbn Dec 08 '11 at 20:40
  • @gbn: Identical in the example. I'll concede that it makes sense mine would degrade more quickly as data sets get larger. – Yuck Dec 08 '11 at 20:43
  • Check this one out http://stackoverflow.com/q/2798094/27535 for more on this. Results could be out of date now of course. And http://stackoverflow.com/q/4230838/27535 – gbn Dec 08 '11 at 21:05
  • @gbn Comment about the WorkTable is spot on. In the end that's going to be the difference. – Yuck Dec 08 '11 at 22:36
0

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.

jht
  • 605
  • 1
  • 6
  • 16
0

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
Farhan
  • 2,535
  • 4
  • 32
  • 54
-1

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
Angelo Fuchs
  • 9,825
  • 1
  • 35
  • 72