Re: Help in getting the first 4 max values

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 9 Jan 2007 04:42:16 -0800
Message-ID: <1168346536.403780.271080@i15g2000cwa.googlegroups.com>

Sandy80 wrote:
> One additional query.
>
> The code suggested above gives the results in differnt rows.
> Is there anyway that the result can be clubbed in one row itself???

It is surprisingly easy to take the original solution that I posted and convert it to output the four possible results per ID on a single row.

The setup (from the previous post):
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);

COMMIT; To place the four possible result rows per ID column into a single row, we will use a combination of MAX and DECODE, when grouping on the ID column.

For example, to find the second highest ranking date per ID, we can use something like this:
SELECT
  ID,
  MAX(DECODE(RANKING,2,ROW_DATE,NULL)) RANKING_DATE_2 FROM
  (SELECT
    ID,
    ROW_DATE,
    ROW_VALUE,
    RANK() OVER (PARTITION BY ID ORDER BY ROW_DATE DESC) RANKING   FROM
    T1)
GROUP BY
  ID;

ID RANKING_DATE_2
1 04-JAN-2001
2 11-JAN-2001
3

By changing the 2 in the SQL statement to a different value (1, 3, or 4) we can obtain the other dates of interest. By changing ROW_DATE to ROW_VALUE, we can obtain the other column of interest. The final SQL statement then becomes:

SELECT
  ID,

  MAX(DECODE(RANKING,1,ROW_DATE,NULL)) RANKING_DATE_1,
  MAX(DECODE(RANKING,1,ROW_VALUE,NULL)) RANKING_VALUE_1,
  MAX(DECODE(RANKING,2,ROW_DATE,NULL)) RANKING_DATE_2,
  MAX(DECODE(RANKING,2,ROW_VALUE,NULL)) RANKING_VALUE_2,
  MAX(DECODE(RANKING,3,ROW_DATE,NULL)) RANKING_DATE_3,
  MAX(DECODE(RANKING,3,ROW_VALUE,NULL)) RANKING_VALUE_3,
  MAX(DECODE(RANKING,4,ROW_DATE,NULL)) RANKING_DATE_4,
  MAX(DECODE(RANKING,4,ROW_VALUE,NULL)) RANKING_VALUE_4

FROM
  (SELECT
    ID,
    ROW_DATE,
    ROW_VALUE,
    RANK() OVER (PARTITION BY ID ORDER BY ROW_DATE DESC) RANKING   FROM
    T1)
WHERE
  RANKING<=4
GROUP BY
  ID;

ID R_DATE_1 R_V_1 R_DATE_2 R_V_2 R_DATE_3 R_V_3 R_DATE_4 R_V_4
 1 05-JAN-2001 19 04-JAN-2001 18 03-JAN-2001 17 02-JAN-2001  16
 2 12-JAN-2001 26 11-JAN-2001 25 10-JAN-2001 24 09-JAN-2001  23
 3 13-JAN-2001 27 Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Jan 09 2007 - 06:42:16 CST