Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle SQL - Find max values within a range of selected values

Oracle Tips by Burleson Consulting

 

Question:  I want to select the last values within selected one minute periods.  Every minute consists of more than 60 data points.

select * from TRADEDETAILS_NSE_FO
WHERE
   SYMBOL='NIFTY'
AND
   INSTRUMENT_TYPE='FUTIDX'
AND

   abs(MONTHS_BETWEEN(TO_DATE(TRADE_DATE,'YYYYMMDD'),TO_DATE(EXPIRY_DATE,'YYYYMMDD'))) <= 1


This searches for the " Near" month future contract, by calculating the difference between the Trade date and the expiry date.

AND TRADE_DATE >= 20060202 AND TRADE_DATE <= 20060202

This is the range of trade date. In this particular case i have reduced it to a single day.

AND (MOD(TO_NUMBER(TO_CHAR(TO_DATE(TRADE_TIME,'HH24:MI:SS'),'Mi')), 5 ) = 0)
order by TRADE_TIME


This divides the trade time in 5 minute time windows and records the value at 5th minute.

After running the query I get the result as:

RUN_NO TRADE_NO SYMBOL INSTRUMENT_TYPE EXPIRY_DATE OPTION_TYPE CORPORATE_ACTION_LEVEL STRIKE_PRICE TRADE_TIME TRADED_PRICE TRADED_QTY TRADE_DATE

3280 3 NIFTY FUTIDX 20060223 FF 0 0 09:55:20 2974.25 100 20060202


Now at this particular time 9:55:20 i have more than 4 trades so imagine the size of the result. Ideally i should get only 67 value per trading day.

After using the Mod function I am getting trades happened " AT" the fifth minute., i.e. trades happened in the duration of 60 secs where as I want only one value of trade at the fifth minute. Hence the query: How to get single row per result?
 

 

Answer:  There may be many ways to do this, but I would create a temporary table to hold the time and value.  Then, you could use a "max" to get the top value for the minute period.

You can use a global temporary table or use the SQL-99 "with" clause to do this.

 

Laurent Schneider has an elegant SQL solution to this problem of selecting the "last" value within a five minute range:

 

select
   max(run_no) keep (dense_rank first order by trade_time,run_no,trade_no) run_no,
   max(trade_no) keep (dense_rank first order by trade_time,run_no,trade_no) trade_no,
   max(SYMBOL) keep (dense_rank first order by trade_time,run_no,trade_no) SYMBOL,
   max(INSTRUMENT_TYPE) keep (dense_rank first order by trade_time,run_no,trade_no) INSTRUMENT_TYPE,
   max(EXPIRY_DATE) keep (dense_rank first order by trade_time,run_no,trade_no) EXPIRY_DATE,
   max(OPTION_TYPE) keep (dense_rank first order by trade_time,run_no,trade_no) OPTION_TYPE,
   max(CORPORATE_ACTION_LEVEL) keep (dense_rank first order by trade_time,run_no,trade_no) CORP_ACT_LEVEL,
   max(STRIKE_PRICE) keep (dense_rank first order by trade_time,run_no,trade_no) STRIKE_PRICE,
   max(TRADE_TIME) keep (dense_rank first order by trade_time,run_no,trade_no) TRADE_TIME,
   max(TRADED_PRICE) keep (dense_rank first order by trade_time,run_no,trade_no) TRADED_PRICE,
   max(TRADED_QTY) keep (dense_rank first order by trade_time,run_no,trade_no) TRADED_QTY,
   max(TRADE_DATE) keep (dense_rank first order by trade_time,run_no,trade_no) TRADE_DATE
from TRADEDETAILS_NSE_FO WHERE SYMBOL='NIFTY' AND INSTRUMENT_TYPE='FUTIDX' AND
abs(MONTHS_BETWEEN(TO_DATE(TRADE_DATE,'YYYYMMDD'),TO_DATE(EXPIRY_DATE,'YYYYMMDD'))) <= 1
AND TRADE_DATE >= 20060202 AND TRADE_DATE <= 20060202
group by trunc(to_char(to_date(TRADE_TIME,'HH24:MI:SS'),'Mi')*5)
order by TRADE_TIME;

Laurent Schneider is the author of the book "Oracle Advanced SQL Programming".


 

 

  
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.