# What is the simplest way of excluding the current row from the result of an analytic?

I want to do something like the following:

``````with w as ( select level as foo,
decode(level,8,1,mod(level,4))*100 as bar
from dual connect by level<9 )
select foo, bar,
min(foo) keep (dense_rank first order by foo) over (partition by bar) as lowest
from w;
/*
FOO                    BAR                    LOWEST
---------------------- ---------------------- ----------------------
4                      0                      4
8                      100                    1
5                      100                    1
1                      100                    1
2                      200                    2
6                      200                    2
3                      300                    3
7                      300                    3
*/
``````

but I want `lowest` to show the next lowest if the current row is the lowest for a given `bar`, or `null` if it is the only one. In other words I want this result:

``````/*
FOO                    BAR                    PSEUDOLOWEST
---------------------- ---------------------- ----------------------
4                      0
1                      100                    5
5                      100                    1
8                      100                    1
2                      200                    6
6                      200                    2
3                      300                    7
7                      300                    3
*/
``````

What is the simplest way of achieving this?

There may be a simpler way, but here is a solution. If the current row is the minimum foo for the bar, then we always want the next (lead) foo. When there is only one foo for a bar the lead will always be null and for every other case we really do want the minimum foo for the bar.

``````with w as (select level as foo, decode(level,8,1,mod(level,4))*100 as bar
from dual connect by level<9)
select foo, bar
, case
when foo = min(foo) over (partition by bar)
then lead(foo) over (partition by bar ORDER BY foo)
else min(foo) over (partition by bar)
end pseudolowest
from w;
``````

Unfortunately `rows excluding current row` doesn't exist.

• Thanks - I think this may be the simplest way. If `rows excluding current row` was allowed wouldn't that give me exactly what I'm after though? I can emulate that in a messy way I'll post as an answer... – Jack Douglas Feb 24 '12 at 7:09
• I've edited the question to use your improved sample data – Jack Douglas Feb 24 '12 at 7:20
• nice. thinking in sets ;) – Florin Ghita Feb 24 '12 at 7:50
• Ah, I understand how you can use it now. I'm not sure how I missed that. Very interesting question by the way. – Leigh Riffel Feb 24 '12 at 14:16
``````with w as ( select level as foo,
decode(level,8,1,mod(level,4))*100 as bar
from dual connect by level<9 )
select foo, bar, nvl(least(lowest1,lowest2),nvl(lowest1,lowest2)) as pseudolowest
from( select foo, bar,
min(foo) over (partition by bar order by foo
rows between unbounded preceding and 1 preceding) as lowest1,
min(foo) over (partition by bar order by foo
rows between 1 following and unbounded following) as lowest2
from w );
/*
FOO                    BAR                    PSEUDOLOWEST
---------------------- ---------------------- ----------------------
4                      0
1                      100                    5
5                      100                    1
8                      100                    1
2                      200                    6
6                      200                    2
3                      300                    7
7                      300                    3
*/
``````
• `lowest1` will be lower than `lowest2` if they both exist, so `nvl(least(lowest1,lowest2),nvl(lowest1,lowest2))` can be simplified to `nvl(lowest1,lowest2)`, and the outermost select is not necessary – mik Feb 1 '17 at 13:25

I like Leigh's approach as it is easy to understand. Here is another one.

``````select foo, bar, to_number(decode(foo, lowest1, lowest2, lowest1)) as pseudolowest
from (select foo, bar, substr(str, 1, instr(str, ',')-1) as lowest1, substr(str, instr(str, ',')+1, instr(str, ',', 1, 2) - instr(str, ',',1,1)-1) as lowest2
from (select foo, bar, listagg(foo, ',') within group (order by foo) over (partition by bar)||',' as str from t)) ;
``````

Of course, this has limitations: a) It will work on 11g (although once can use other string aggregation techniques for versions prior to 11g) b) It will not work if aggregated string goes over 4000 characters.

But, as I said, just another way... :)