SQL – RANK, MAX Analytical Functions, DECODE, SIGN

26 12 2009

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  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

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

Actions

Information

3 responses

27 12 2009
jametong

for this type of question, I will prefer to user case instead of decode and sign..

SQL> SELECT
  2    SYMBOL,
  3    MAX(ORDERS) OVER (PARTITION BY SYMBOL) ORDERS,
  4    TTIME TTIME,
  5    case
  6      when TTIME >= 9450 and ttime <= 15300 then 10
  7      else 1
  8    end extra_sort
  9  FROM
 10    T1;

S     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

10 rows selected.
27 12 2009
Charles Hooper

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.

5 02 2012
http://etllabs.com/

It’s very clean explanation with progressive teaching approach, nce explanation on Oracle Decode

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 139 other followers

%d bloggers like this: