Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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
add comment
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        
*/
share|improve this answer
add comment

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... :)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.