Re: select next highest salary or last login date ...

Thanks!!! This'll work.

mcstock wrote:
> logically, you're asking for
>
> the (sort of) maximum value of (XXX) within group ZZZZZ
> where the value is less than the actual maximum value of (XXX) within group
> ZZZZ
>
> figure out the individual steps need to solve the problem, then figure out
> how to plug them together into one SQL
>
>
> approach 1:
> ------------
> select max(xxxx) from yyyyy where the_grouping = 'ZZZZZ'
>
> select max(xxxx) from yyyyy where the_grouping = 'ZZZZZ' and xxxx < the
> value i just got in my previous query
>
> :: uses as subquery in the WHERE clause
>
>
> approach 2:
> ------------
> select the top two values of xxxx from yyyyy where the_grouping = 'ZZZZZ',
> sorted in descending order; when reading the results, ignore the first row
> and keep the second one
>
> :: uses a subquery in the FROM clause and the ROWNUM pseudo column -- less
> intuitive but better performance than the first approach; you'll limit the
> subquery rows with ROWNUM, and return ROWNUM (with an alias) so that you can
> reference it in the outer query
>
>
> let me know if that helps you figure out the query structure, or if you'd
> like me to post examples