CODEWITHSUNDEEP
sqloracle-database
Alvin Thompson
Alvin Thompson

Reputation: 5448

get the nth-lowest value in a `group by` clause

Here's a tough one: I have data coming back in a temporary table foo in this form:

id   n    v
--   -    -
1    3    1
1    3    10
1    3    100
1    3    201
1    3    300
2    1    13
2    1    21
2    1    300
4    2    1
4    2    7
4    2    19
4    2    21
4    2    300
8    1    11

Grouping by id, I need to get the row with the nth-lowest value for v based on the value in n. For example, for the group with an ID of 1, I need to get the row which has v equal to 100, since 100 is the third-lowest value for v.

Here's what the final results need to look like:

id   n    v
--   -    -
1    3    100
2    1    13
4    2    7
8    1    11

Some notes about the data:

Bonus points if you can do it in generic SQL instead of oracle-specific stuff, but that's not a requirement (I suspect that rownum may factor prominently in any solutions). It has in my attempts, but I wind up confusing myself before I get a working solution.

Upvotes: 1

Views: 1018

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

If your database is version 12.1 or higher then there is a much simpler solution:

SELECT DISTINCT ID, n, NTH_VALUE(v,n) OVER (PARTITION BY ID) AS v
FROM foo
ORDER BY ID;


| ID | N |   V |
|----|---|-----|
|  1 | 3 | 100 |
|  2 | 1 |  13 |
|  4 | 2 |   7 |
|  8 | 1 |  11 |

Depending on your real data you may have to add an ORDER BY n clause and/or windowing_clause as RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, see NTH_VALUE

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use window function

select * from
(
select t.*, row_number() over(partition by id ,n order by v) as rn
from foo  t
 ) t1 
 where t1.rn=t1.n

as ops sample output just need 3rd highest value so i put where condition t1.rn=3 though accodring to description it would be t1.rn=t1.n

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=65abf8d4101d2d1802c1a05ed82c9064

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

I would use row_number function make row number the compare with n column value in CTE, do another CTE to make row number order by v desc.

get rn = 1 which is mean max value in the n number group. 

CREATE TABLE foo(
   id int,
   n int,
   v int
);


insert into foo values (1,3,1);
insert into foo values (1,3,10);
insert into foo values (1,3,100);
insert into foo values (1,3,201);
insert into foo values (1,3,300);
insert into foo values (2,1,13);
insert into foo values (2,1,21);
insert into foo values (2,1,300);
insert into foo values (4,2,1);
insert into foo values (4,2,7);
insert into foo values (4,2,19);
insert into foo values (4,2,21);
insert into foo values (4,2,300);
insert into foo values (8,1,11);

Query 1:

with cte as(
    select id,n,v 
    from
    (
        select t.*, row_number() over(partition by id ,n order by n) as rn
        from foo t
    ) t1 
    where rn <= n
), maxcte as (
    select id,n,v, row_number() over(partition by id ,n order by v desc) rn 
    from cte 
)
select id,n,v 
from maxcte
where rn = 1

Results:

| ID | N |   V |
|----|---|-----|
|  1 | 3 | 100 |
|  2 | 1 |  13 |
|  4 | 2 |   7 |
|  8 | 1 |  11 |

Upvotes: 2

Related Questions