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

good...

couple comments on approach 2:

it actually requires a double in-line view (from clause sub-query), the inner one is sorted, the next outer grabs the first 2 rows and returns the aliased ROWNUM, the outer throws away the first row

also, the innermost query must select distinct salaries, so that it returns the 2nd highest value, not the highest salary from the 2nd row in which the highest salary was found

• mcs

"Mark" <mark_at_nowhere.com> wrote in message news:K8mdnVnUFsJqlVGiRVn-hA_at_wideopenwest.com...
| 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
| >
|
Received on Tue Dec 02 2003 - 14:25:48 CET