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

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

-- 
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com


"Mark" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
| good spam subject ;).
|
| anyway, i'm  alittle stumped.  i'm in need of putting together a query
| that gets the next highest salary ( select max ( sal ) - 1?, from an
| emp_sal type table. another example is if i have a login_history table,
| and i'm logged in, but i want to display the date of my previous login.
|   if the login_history table has user_id and login_date which makes up
| the primary key,  how can i get the most recent login (minus the most
| recent one?).
|
| the max function comes to mind and use of the rowid/rownum colums, but
| is there an easier, more obvious way to do this perhaps?
|