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

[AndrewWiggins99]

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)

 

[Andrzejek]

‘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.

 

[AndrewWiggins99]

Before and after based on Amount Date (Col2)

 

[Andrzejek]

Let's say you have this data:
[pre]
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
[/pre]
Which 5 records do you want as the outcome and why?

Or you have this data (first 5 records from above):
[pre]
Amount Amount Date
123 1/1/2010
4500 5/5/2010
5500 5/5/2010
5678 5/5/2010
10000 6/6/2010
[/pre]
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.

 

[AndrewWiggins99]

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)

 

[Andrzejek]

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. :-(

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.

 

[taupirho]

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.