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

From: mcstock <mcstockspamplug_at_spamdamenquery.com>
Date: Mon, 1 Dec 2003 22:18:16 -0500
Message-ID: <8bGdnY-w7aIGmVGi4p2dnA_at_comcast.com>

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" <mark_at_nowhere.com> wrote in message
news:95qdnTXq_YDFa1aiRVn-iQ_at_wideopenwest.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?


|

Received on Tue Dec 02 2003 - 04:18:16 CET