Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (Oracle 10g, Windows XP)
Query Help [message #297689] Sat, 02 February 2008 08:41 Go to next message
niks_13
Messages: 3
Registered: February 2008
Junior Member
Hello all,


I have the following data in a table (it has been pre-processed)

Time | Exchange | Bid | Ask

9:30 | P |76.26 | 76.45

9:31 | P |76.21 | 76.39

9:32 | C |75.00 | 76.6
9:32 | N |75.51 | 75.27
9:32 | P |75.57 | 76.54

9:33 | P |75.93 | 76.49

and so on with the time going on until 4:00PM

Now, what I want to do is to extract only 1 row for each minute based on the maximum value of the bid price and the minimum value of the ask price for the latest available exchange. In other words, for 9:30, since only exchange P is available, I should get

9:30 | 76.26 | 76.45

For 9:31, again only P is available, which overwrites the P from 9:30; so at 9:31, I have:

9:31 | 76.21 | 76.39

For 9:31, I have C and N which are the first time they are available, and I have a new P which has now overwritten the P from 9:31. So at 9:32, I have

9:32 | 75.57 | 76.27 [highest BID from exchange P and lowest ASK from exchange N]


Now, at 9:33, I only have exchange P which has overwritten the P from 9:32. But I still have exchanges N and C which are valid from 9:32. So, at 9:33, I have exchange P from 9:33 and exchanges N and C from 9:32. From this set, it is easy to see that:

9:33 | 75.93 | 75.27 [highest BID from exchange P at 9:33 and lowest ASK from exchange N at 9:32]


I am at a loss as to what kind of SQL (or PL SQL) I need to write to accomplish this task and would be grateful if someone can help me.

Thanks.
Re: Query Help [message #297692 is a reply to message #297689] Sat, 02 February 2008 09:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at row_number function.
You'll find it in the documentation, I'm tired to put a link to it.

Regards
Michel
Re: Query Help [message #297708 is a reply to message #297692] Sat, 02 February 2008 12:26 Go to previous messageGo to next message
niks_13
Messages: 3
Registered: February 2008
Junior Member
Thanks for the reply; I tried using the row_number function but I couldn't come up with a useful result. This is the query I wrote (I am not a developer, so I might have been a bit off):

select TIME, BID , ASK , EXCHANGE,
row_number() over (partition by Time order by Exchange) as ID
from aggr_by_min

The result I get is as below:

Time | Exchange | Bid | Ask | ID

9:30 | P |76.26 | 76.45 | 1

9:31 | P |76.21 | 76.39 | 1

9:32 | C |75.00 | 76.6 | 1
9:32 | N |75.51 | 75.27 | 2
9:32 | P |75.57 | 76.54 | 3

9:33 | P |75.93 | 76.49 | 1

At 9:32, exchange P has an ID of 3 while it has an ID of 1 for other minutes. So at 9:33, I still won't be able to get the lowest value of Ask as 75.27 which is available at exchange N at 9:32.

Any suggestions?
Re: Query Help [message #297709 is a reply to message #297708] Sat, 02 February 2008 12:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reverse the order (use a descending order) and get the number 1.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Query Help [message #297711 is a reply to message #297709] Sat, 02 February 2008 12:51 Go to previous messageGo to next message
niks_13
Messages: 3
Registered: February 2008
Junior Member
Thanks, it works on this small set, but I should have mentioned that the exchanges in the actual file could be any of the following: T , Q , P , N and C. Each time bucket (i.e. every minute) could have one or more of these exchanges.

So at 10:00, I might have T and N (which will give the ID values of 1 and 2 to T and N) while at 10:01, I might have T , P and C (which will give the ID values of 1, 2, 3 to T, P, C). This will make the ID = 2 different for different exchanges at different times.

Re: Query Help [message #297712 is a reply to message #297711] Sat, 02 February 2008 13:37 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your problem but you have the way to do it, just change what does not work.
If you want more help, you have to ost a test case: create table and insert statements and the result you want with these data and why.

Regards
Michel

Previous Topic: Grant permission
Next Topic: Order by result using instr
Goto Forum:
  


Current Time: Fri Dec 09 03:55:42 CST 2016

Total time taken to generate the page: 0.09582 seconds