2 Replies Latest reply on Feb 23, 2011 12:50 PM by Frank Kulash

    to select latest three records for each attribute

    842086
      Hi,
      Im having the table like this. in this i want to take latest 3 records for each attribute
      if any one can know please help me.


      user_id     party_name                         amt     attribute     date
           
           6018     YES BANK LTD-CONNECTIVITY               640     Corporate     3/30/2011     
           5865     Altruista Health Services Private Limited     100000 Corporate     3/3/2011     
           14164     Atree                              275000     Channel          3/2/2011     
           12675     RAYMOND LIMITED-HOSTING     4900000     Strategy     2/27/2011     
           7534     MERIDIUM TECHNOLOGY - ACCESS BPO 1150000     Corporate     2/24/2011     
           13186     Lumax Industries                    3360000     Corporate     2/24/2011     
           15527     Lumax Industries                    3360000     Corporate     2/24/2011     
           14605     Lex Transformers Pvt Ltd               135000     Channel          2/21/2011     
           14605     Udayanthi Software Solutions          40000     Channel          2/21/2011     
           13186     Micromax India                         200000     Corporate     2/3/2011     
           13188     Micromax India                         200000     Corporate     2/3/2011     
           15365     JINDAL MEDICOT LTD-CONNECTIVITY     0     Channel          1/22/2011     
           6598     GKB RX LENS PVT LTD-CONNECTIVITY          49500     Strategy     1/17/2011     
           6598     Paradeep Phosphate Limited.PPL               550000     Strategy     1/17/2011


      Thanks & Regards,
      Priya .

      Edited by: 839083 on Feb 23, 2011 4:34 AM
        • 1. Re: to select latest three records for each attribute
          €$ħ₪
          SELECT *FROM
          (
          SELECT ATTRIBUTE , DATE ,ROW_NUMBER() OVER(PARTITION BY ATTRIBUTE ORDER BY DATE DESC ) RN FROM TABLE_NAME
          )
          WHERE RN <=3;
          1 person found this helpful
          • 2. Re: to select latest three records for each attribute
            Frank Kulash
            Hi,

            Welcome to the forum!

            Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements), and the results you want from that data. Explain how you get the resutls you want from that data.
            If you can use commonly available tables (like those in the scott schema) to show your problem, then you don't have to post the sample data; just post the results you want from that data and the explanation.
            For example, I think you want to do something very much like this:

            "I want to show only 3 rows from each department in the scott.emp table, the 3 rows rows with the latest hiredates. If there happens to be a tie (2 or more people in the same department with the same hiredate), then I want the one with the first ename (in alphabetic order) to be considered later than the others. The reuslts I want are:
            {code}
            ` DEPTNO ENAME HIREDATE
            ---------- ---------- ---------
            10 MILLER 23-JAN-82
            10 KING 17-NOV-81
            10 CLARK 09-JUN-81
            20 ADAMS 23-MAY-87
            20 SCOTT 19-APR-87
            20 FORD 03-DEC-81
            30 JAMES 03-DEC-81
            30 MARTIN 28-SEP-81
            30 TURNER 08-SEP-81
            {code}
            I'm using Oracle 10.2.0.1.0."

            Here's one way to do that:
            WITH     got_r_num     AS
            (
                 SELECT     deptno, ename, hiredate
                 ,     ROW_NUMBER () OVER ( PARTITION BY  deptno
                                     ORDER BY      hiredate     DESC
                                     ,             ename
                                   )     AS r_num
                 FROM     scott.emp
            )
            SELECT       deptno, ename, hiredate
            FROM       got_r_num
            WHERE       r_num          <= 3
            ORDER BY  deptno
            ,       hiredate     DESC
            ,       ename
            ;
            This is called a Top-N Query , because you want N items (N=3 in this case) from the top of a sorted list.

            The query above will work in Oracle 9 (and up).

            Edited by: Frank Kulash on Feb 23, 2011 7:41 AM
            1 person found this helpful