Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    18

    Unanswered: TOP row for each group

    Hi All,

    I want to fetch top 2 rows from each group (Oracle 8i). The sample data (schema as well) given below.

    s_type s_no s_date
    A 1 01/01/2005
    A 1 04/01/2005
    A 1 31/01/2005
    A 2 01/01/2005
    B 1 01/01/2005
    B 1 02/02/2005
    B 1 02/02/2005
    B 1 05/08/2005

    The result should be
    A 1 04/01/2005
    A 1 31/01/2005
    A 2 01/01/2005
    B 1 02/02/2005
    B 1 02/02/2005
    B 1 05/08/2005

    I want to full out each groups (s_type, s_no) top 2 date's all rows.

    I try 2nd max find query (that is commonly used to find nth top row) that gives null when there is more than one row for a group in 2nd position.

    I tried rand() function also.


    Can any one help me!

    Thanks iad,
    Anbazhagan

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use analytic functions.

    Something like:
    Code:
    select s_type, s_no, s_date
    from
    ( select s_type, s_no, s_date
      ,      row_number() over (partition by s_type, s_no order by s_date desc) rn
      from   mytable
    )
    where rn <= 2;

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Haven't tried it yet, but I believe that will fail on case of group B 1 02/02/2005, since it will assign a row_number() of 1 and 2 to both dates, being the same. Try using rank() instead of row_number() and it should be ok.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You have that backwards. RANK will give both rows the same rank, but ROW_NUMBER will be different (arbitrarily) for each row:
    Code:
    SQL> select ename, deptno, job
      2  ,      rank() over (partition by deptno order by job) rnk
      3  ,      row_number() over (partition by deptno order by job) rn
      4  from emp;
    
    ENAME          DEPTNO JOB              RNK         RN
    ---------- ---------- --------- ---------- ----------
    MILLER             10 CLERK              1          1
    CLARK              10 MANAGER            2          2
    KING               10 PRESIDENT          3          3
    SCOTT              20 ANALYST            1          1
    FORD               20 ANALYST            1          2
    SMITH              20 CLERK              3          3
    ADAMS              20 CLERK              3          4
    JONES              20 MANAGER            5          5
    JAMES              30 CLERK              1          1
    BLAKE              30 MANAGER            2          2
    ALLEN              30 SALESMAN           3          3
    WARD               30 SALESMAN           3          4
    MARTIN             30 SALESMAN           3          5
    TURNER             30 SALESMAN           3          6

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Doesn't correspond to users' desired output then.
    Code:
    SQL> r
      1  select s_type, s_no, s_date
      2  from
      3  ( select s_type, s_no, s_date
      4    ,      row_number() over (partition by s_type, s_no order by s_date desc) rn
      5    from   t
      6  )
      7* where rn <= 2
    
    S       S_NO S_DATE
    - ---------- ----------
    A          1 31/01/2005
    A          1 04/01/2005
    A          2 01/01/2005
    B          1 05/08/2005
    B          1 02/02/2005
    Changing row_number to rank, and I got.
    Code:
    SQL> r
      1  select s_type, s_no, s_date
      2  from
      3  ( select s_type, s_no, s_date
      4    ,      rank() over (partition by s_type, s_no order by s_date desc) rn
      5    from   t
      6  )
      7* where rn <= 2
    
    S       S_NO S_DATE
    - ---------- ----------
    A          1 31/01/2005
    A          1 04/01/2005
    A          2 01/01/2005
    B          1 05/08/2005
    B          1 02/02/2005
    B          1 02/02/2005

  6. #6
    Join Date
    Jan 2005
    Posts
    18
    Thanks Martinez & Adndrewst,

    I 'ill try this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •