select next highest salary or last login date ...

[Mark]

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?

[mcstock]

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" <ma**@nowhere.c om> wrote in message
news:95******** ************@wi deopenwest.com. ..
| 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?
|

[Mark]

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

[mcstock]

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" <ma**@nowhere.c om> wrote in message
news:K8******** ************@wi deopenwest.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
| >
|

[Dario]

Problem just calls for analytic functions:

select * from
(
select e.*, rank() over (order by sal desc) rn
from emp e )
where rn = 2


Mark <ma**@nowhere.c om> wrote in message news:<95******* *************@w ideopenwest.com >...

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?