Re: Help in getting the first 4 max values

Sandy80 wrote:
> Hi,
>
> I need help regarding writing an sql query for getting the first 4 max
> values in the following scenario.
> I have a table which has a data like this:
>
> Row No. Id Date Value
> 1 1 1/1/2001 15
> 2 1 1/2/2002 16
> 3 1 1/3/2003 17
> 4 1 1/4/2004 18
> 5 1 1/5/2005 19
> 6 2 1/6/2006 20
> .....
>
> Now what I need are the records with the last 4 date values. That is I
> need the values in the column "Date" and the values in the column
> "value" for the last 4 dates for the Id=1 and similarly for Id=2 and so
> on. So in other words I would need the Date and Value records for row
> no. 2,3,4 and 5.
> Can anyone help me in writing a query for extracting this data.
> Thanks in advance.
>
> Cheers
> Sandy

Possibly the best way to do this is to use either the RANK or DENSE_RANK analytical functions. The setup:

CREATE TABLE T1 (
  ROW_NO NUMBER(10),
  ID NUMBER(10),
  ROW_DATE DATE,
  ROW_VALUE NUMBER(10));

INSERT INTO T1 VALUES (1,1,'01-JAN-2001',15);
INSERT INTO T1 VALUES (2,1,'02-JAN-2001',16);
INSERT INTO T1 VALUES (3,1,'03-JAN-2001',17);
INSERT INTO T1 VALUES (4,1,'04-JAN-2001',18);
INSERT INTO T1 VALUES (5,1,'05-JAN-2001',19);
INSERT INTO T1 VALUES (6,2,'06-JAN-2001',20);
INSERT INTO T1 VALUES (7,2,'07-JAN-2001',21);
INSERT INTO T1 VALUES (8,2,'08-JAN-2001',22);
INSERT INTO T1 VALUES (9,2,'09-JAN-2001',23);
INSERT INTO T1 VALUES (10,2,'10-JAN-2001',24);
INSERT INTO T1 VALUES (11,2,'11-JAN-2001',25);
INSERT INTO T1 VALUES (12,2,'12-JAN-2001',26);
INSERT INTO T1 VALUES (13,3,'13-JAN-2001',27);

You will note that I added additional rows to the table, which should help you see what is happening at each step.

If we simply include the RANK analytic function in a SELECT statement, such as this:
SELECT
  ID,
  ROW_DATE,
  ROW_VALUE,
  RANK() OVER (PARTITION BY ID ORDER BY ROW_DATE DESC) RANKING FROM
  T1;

Oracle separates the rows in the table by the ID column due to the PARTITION BY clause, and then sorts the results within each subset of rows by the ROW_DATE in descending order.

  ID ROW_DATE ROW_VALUE RANKING

  1  05-JAN-2001 	19	    1
  1  04-JAN-2001 	18	    2
  1  03-JAN-2001 	17	    3
  1  02-JAN-2001 	16	    4
  1  01-JAN-2001 	15	    5
  2  12-JAN-2001 	26	    1
  2  11-JAN-2001 	25	    2
  2  10-JAN-2001 	24	    3
  2  09-JAN-2001 	23	    4
  2  08-JAN-2001 	22	    5
  2  07-JAN-2001 	21	    6
  2  06-JAN-2001 	20	    7
  3  13-JAN-2001 	27	    1

If we then slide the above into an inline view, we can return only those rows with an RANKING of 4 or less. SELECT
  ID,
  ROW_DATE,
  ROW_VALUE
FROM
  (SELECT
    ID,
    ROW_DATE,
    ROW_VALUE,
    RANK() OVER (PARTITION BY ID ORDER BY ROW_DATE DESC) RANKING   FROM
    T1)
WHERE
  RANKING<=4;

  ID ROW_DATE ROW_VALUE

  1  05-JAN-2001 	19
  1  04-JAN-2001 	18
  1  03-JAN-2001 	17
  1  02-JAN-2001 	16
  2  12-JAN-2001 	26
  2  11-JAN-2001 	25
  2  10-JAN-2001 	24
  2  09-JAN-2001 	23
  3  13-JAN-2001 	27

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jan 04 2007 - 06:35:00 CST