December 26, 2009
A couple years ago the following question appeared on the comp.databases.oracle.misc Usenet group:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/22b36e3ca18490db
Table Structure:
Table1 symbol orders ttimeRequirement: Want to arrange all records, symbolwise, based on orders (asc order).
Among that, if a particular symbol have records in the range TTIME BETWEEN 9300 AND 1530, then I want to extract MIN(TTIME) within that range else MIN(TTIME) of whatever available records.I want to achieve this using a single query.
Example:
Table1 symbol orders ttime A 2 9300 A 2 9450 A 2 1030 A 2 9451 A 2 1530 A 2 1600 A 2 1700 B 3 9300 B 4 1600 C 3 1600I want to get all records with maximum orders (in desc order) for each symbol.
Output: Symbol Orders ttime A 2 9300 A 2 9450 A 2 9451 A 2 1030 A 2 1530 A 2 1600 A 1 9300 A 2 1700 B 4 9300 B 4 1600 C 3 1600Out of this subset I want to get all records with ttime falling between 9450 to 1530 to appear first in asc. if there is no record within this range then I want to go for normal asc order on ttime.
Ouput: Symbol Orders ttime A 2 9450 A 2 1030 A 2 1530 A 2 1600 A 2 9300 B 4 9450 B 4 1030 B 4 1600 C 3 1600Finally I want to extract only first record
Final output: A 2 9450 B 4 9300 C 3 1600
Are we dealing with time here? When is 9300 less than 1530? Why is 1 included in the above?
The set up:
CREATE TABLE T1 ( SYMBOL CHAR(1), ORDERS NUMBER(10), TTIME NUMBER(10)); INSERT INTO T1 VALUES('A',2,9300); INSERT INTO T1 VALUES('A',2,9450); INSERT INTO T1 VALUES('A',2,10300); INSERT INTO T1 VALUES('A',2,9451); INSERT INTO T1 VALUES('A',2,15300); INSERT INTO T1 VALUES('A',2,16000); INSERT INTO T1 VALUES('A',2,17000); INSERT INTO T1 VALUES('B',3,9300); INSERT INTO T1 VALUES('B',4,16000); INSERT INTO T1 VALUES('C',3,16000);
First, let’s find the maximum value for ORDERS for each SYMBOL:
SELECT SYMBOL, MAX(ORDERS) OVER (PARTITION BY SYMBOL) ORDERS, TTIME TTIME FROM T1; SYMBOL ORDERS TTIME A 2 9300 A 2 9450 A 2 10300 A 2 9451 A 2 15300 A 2 17000 A 2 16000 B 4 16000 B 4 9300 C 3 16000
You stated that if TTIME is between 9450 and 1530 (should it be 15300?), that it should take priority over other values of TTIME. The DECODE below determines if TTIME is between 9450 and 15300, if so it assigns a value of 10 to EXTRA_SORT, otherwise it assigns a value of 1 to EXTRA_SORT:
SELECT SYMBOL, MAX(ORDERS) OVER (PARTITION BY SYMBOL) ORDERS, TTIME TTIME, DECODE(SIGN(TTIME-9449.999),1,DECODE(SIGN(TTIME-15300.0001),-1,10,1),1) EXTRA_SORT FROM T1; SYMBOL ORDERS TTIME EXTRA_SORT A 2 9300 1 A 2 9450 10 A 2 10300 10 A 2 9451 10 A 2 15300 10 A 2 17000 1 A 2 16000 1 B 4 16000 1 B 4 9300 1 C 3 16000 1
If we slide the above into an inline view, we can then rank the rows when sorted first on EXTRA_SORT and then on TTIME:
SELECT SYMBOL, ORDERS, TTIME, RANK() OVER (PARTITION BY SYMBOL ORDER BY EXTRA_SORT DESC,TTIME) POSITION FROM (SELECT SYMBOL, MAX(ORDERS) OVER (PARTITION BY SYMBOL) ORDERS, TTIME TTIME, DECODE(SIGN(TTIME-9449.999),1,DECODE(SIGN(TTIME-15300.0001),-1,10,1),1) EXTRA_SORT FROM T1); SYMBOL ORDERS TTIME POSITION A 2 9450 1 A 2 9451 2 A 2 10300 3 A 2 15300 4 A 2 9300 5 A 2 16000 6 A 2 17000 7 B 4 9300 1 B 4 16000 2 C 3 16000 1
We can again slide the above into an inline view and extract only those with a POSITION value of 1:
SELECT SYMBOL, ORDERS, TTIME FROM (SELECT SYMBOL, ORDERS, TTIME, RANK() OVER (PARTITION BY SYMBOL ORDER BY EXTRA_SORT DESC,TTIME) POSITION FROM (SELECT SYMBOL, MAX(ORDERS) OVER (PARTITION BY SYMBOL) ORDERS, TTIME TTIME, DECODE(SIGN(TTIME-9449.999),1,DECODE(SIGN(TTIME-15300.0001),-1,10,1),1) EXTRA_SORT FROM T1) ) WHERE POSITION=1; SYMBOL ORDERS TTIME A 2 9450 B 4 9300 C 3 16000
for this type of question, I will prefer to user case instead of decode and sign..
Great idea, thanks for the suggestion. I agree that the CASE structure is easier to understand (and maintain) than the equivalent DECODE structure in a SQL statement like the one used in this blog post.
It’s very clean explanation with progressive teaching approach, nce explanation on Oracle Decode
SELECT empno, deptno, sal,
min(sal) over(order by deptno) AS avg_sal
FROM emp;
if using order by I am getting unexpected result..please explain me how it works
An ORDER BY clause in that analytic function results in a running MIN calculation, only considering the rows encountered so far when the rows are processed in order by the DEPTNO column.