INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query - Two records before High Amount and Two Records after High Amount

Query - Two records before High Amount and Two Records after High Amount

Query - Two records before High Amount and Two Records after High Amount

(OP)
Hello,

How can I query:

1) Return the Highest Amount (col1) and return both Amount (Col1) and Amount Date (Col2)
2) Return the two records before that Amount Date (Col2) from Item 1
3) Return the two records after that Amount Date (Col2) from Item 1

There should be 5 records total:
The Highest Amount plus the two records before the highest amount and two records after the highest amount


For 1) I used MAX(col1)... but not sure how to add 2) and 3)

RE: Query - Two records before High Amount and Two Records after High Amount

‘Before’ and ‘after’ suggests some kind of ‘order’ of your records.
If you already are getting the MAX() of something, how can you have anything greater than your MAX() value?

Could you show some sample of your data, let’s say 10 records, and expected result?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Query - Two records before High Amount and Two Records after High Amount

(OP)
Before and after based on Amount Date (Col2)

RE: Query - Two records before High Amount and Two Records after High Amount

Let's say you have this data:

Amount    Amount Date 
  123      1/1/2010
 4500      5/5/2010
 5500      5/5/2010
 5678      5/5/2010
10000      6/6/2010
 2500      8/8/2010
 3500      8/8/2010
 5000      8/8/2010
 
Which 5 records do you want as the outcome and why?

Or you have this data (first 5 records from above):

Amount    Amount Date 
  123      1/1/2010
 4500      5/5/2010
 5500      5/5/2010
 5678      5/5/2010
10000      6/6/2010
 
Same question as above.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Query - Two records before High Amount and Two Records after High Amount

(OP)
Amount Amount Date
123 1/1/2010
4500 2/5/2010
5500 3/5/2010
5678 4/5/2010
10000 6/6/2010
2500 8/8/2010
3500 12/8/2010
5000 13/8/2010

Expected Result:
3500 12/8/2010 (first record (date of highest amount +2 record)
2500 8/8/2010 (first record (date of highest amount +1 record)
10000 6/6/2010 (highest amount
5678 4/5/2010 (first record (date of highest amount -1 record)
5500 3/5/2010 (first record (date of highest amount -2 record)


RE: Query - Two records before High Amount and Two Records after High Amount

The data and the outcome you presented is not based on the data I posted. I will have to give up this tread because if you choose to change the given data, there is nothing I can help you with. sad

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Query - Two records before High Amount and Two Records after High Amount

This is inelegant and wont work in a lot of circumstances, but it works on your particular data set. If I had the time I could make it better but over to you now to improve and make work more generally with your data !

SQL> select * from tom;

AMOUNT AM_DATE
---------- ---------
123 01-JAN-10
4500 02-MAY-10
5500 03-MAY-10
5678 04-MAY-10
10000 06-MAY-10
2500 08-AUG-10
3500 12-AUG-10
5000 13-AUG-10

8 rows selected.


with x as
(
select am_date,rn2,rn3,rn ,amount, max_am
from
(
select am_date,row_number() over(ORDER BY am_date) rn , row_number() over(ORDER BY am_date) -2 rn2,
row_number() over(ORDER BY am_date) + 2 rn3,amount,max(amount) over() max_am
from tom
)
),
y as
(
select rn2,rn3,rn ,amount, max_am
from
(
select row_number() over(ORDER BY am_date) rn , row_number() over(ORDER BY am_date) -2 rn2,
row_number() over(ORDER BY am_date) + 2 rn3,amount,max(amount) over() max_am
from tom
)
where max_am = amount
)
select x.amount,x.am_date
from x,y
where x.rn between y.rn2 and y.rn3
order by x.rn desc


SQL> /

AMOUNT AM_DATE
---------- ---------
3500 12-AUG-10
2500 08-AUG-10
10000 06-MAY-10
5678 04-MAY-10
5500 03-MAY-10

SQL>

In order to understand recursion, you must first understand recursion.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close