Charles Hooper wrote:
> pankaj_wolfhun
...@yahoo.co.in wrote:
> > Greetings,
> > 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
> 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.
Thanks Charles. Thats exactly what I needed. Thanks again.
