2 Replies Latest reply: Feb 23, 2011 6:50 AM by Frank Kulash RSS

    to select latest three records for each attribute

      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
          WHERE RN <=3;
          • 2. Re: to select latest three records for each attribute
            Frank Kulash

            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:
            ---------- ---------- ---------
            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
            I'm using Oracle"

            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