Help Need to build this logic
pankaj_w...@yahoo.co.in
Table Structure:
Table1
symbol orders ttime
Requirement: 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 1600
I 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 1600
Out 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 1600
Finally I want to extract only first record
Final output:
A 2 9450
B 4 9300
C 3 1600
Any help would be appreciated.
TIA
Charles Hooper
Several entries in your post do not make logical sense:
* BETWEEN 9300 AND 1530
* > A 2 1600
> A 1 9300
> A 2 1700
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, lets 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
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
pankaj_w...@yahoo.co.in
Thanks Charles. Thats exactly what I needed. Thanks again.