3 Replies Latest reply: Jan 30, 2013 6:37 AM by AlbertoFaenza RSS

    need to get latest record

    954475
      Hi,

      I need to query table and get latest value of col1, col2 and col3 based on grain of col4, col5 and col6. Latest value is to be get by latest value of a date field say UPDDT. Apart from these there are 2-3 more columns like col7, col8, col9. I believe we need to use analytic function here like:

      SELECT COL7, COL8, COL9,
      MAX(COL1) OVER (PARTITION BY COL4, COL5, COL6 ORDER BY UPDDT DESC NULLS LAST),
      MAX(COL2) OVER (PARTITION BY COL4, COL5, COL6 ORDER BY UPDDT DESC NULLS LAST),
      MAX(COL3) OVER (PARTITION BY COL4, COL5, COL6 ORDER BY UPDDT DESC NULLS LAST),
      FROM TABLE1;

      But only problem is I needn't get max value but only the latest value based on UPDDT. Can anybody help? Which function I can use here to get only latest record on this date column? Can I use FIRST VALUE?
        • 1. Re: need to get latest record
          Frank Kulash
          Hi,
          951472 wrote:
          ... Can I use FIRST VALUE?
          Yes: use FIRST_VALUE:
          SELECT     col7, col8, col9
          ,     FIRST_VALUE (col1) OVER ( PARTITION BY  col4, col5, col6
                                       ORDER BY       upddt  DESC  NULLS LAST
                              ) AS last1
          ,     FIRST_VALUE (col2) OVER ( PARTITION BY  col4, col5, col6
                                       ORDER BY       upddt  DESC  NULLS LAST
                              ) AS last2
          ,     FIRST_VALUE (col3) OVER ( PARTITION BY  col4, col5, col6
                                       ORDER BY       upddt  DESC  NULLS LAST
                              ) AS last3
          FROM    table1
          ;
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: need to get latest record
            AlbertoFaenza
            951472 wrote:
            Hi,

            I need to query table and get latest value of col1, col2 and col3 based on grain of col4, col5 and col6. Latest value is to be get by latest value of a date field say UPDDT. Apart from these there are 2-3 more columns like col7, col8, col9. I believe we need to use analytic function here like:

            SELECT COL7, COL8, COL9,
            MAX(COL1) OVER (PARTITION BY COL4, COL5, COL6 ORDER BY UPDDT DESC NULLS LAST),
            MAX(COL2) OVER (PARTITION BY COL4, COL5, COL6 ORDER BY UPDDT DESC NULLS LAST),
            MAX(COL3) OVER (PARTITION BY COL4, COL5, COL6 ORDER BY UPDDT DESC NULLS LAST),
            FROM TABLE1;

            But only problem is I needn't get max value but only the latest value based on UPDDT. Can anybody help? Which function I can use here to get only latest record on this date column? Can I use FIRST VALUE?
            The correct function to use is LAST_VALUE.

            Here is an example using last value together with IGNORE NULLS on emp table:
              SELECT empno
                   , mgr
                   , deptno
                   , comm
                   , LAST_VALUE (comm) IGNORE NULLS OVER (PARTITION BY deptno ORDER BY empno) comm_last_value
                FROM emp
            ORDER BY deptno, empno;
            
                 EMPNO        MGR     DEPTNO       COMM COMM_LAST_VALUE
            ---------- ---------- ---------- ---------- ---------------
                  7782       7839         10                           
                  7839                    10                           
                  7934       7782         10                           
                  7369       7902         20                           
                  7566       7839         20                           
                  7788       7566         20                           
                  7876       7788         20                           
                  7902       7566         20                           
                  7499       7698         30        300             300
                  7521       7698         30        500             500
                  7654       7698         30       1400            1400
                  7698       7839         30                       1400
                  7844       7698         30          0               0
                  7900       7698         30                          0
            If you need additional info post some sample data for your case.

            When you put some code or output please enclose it between two lines starting with {noformat}
            {noformat}
            
            i.e.:
            {noformat}
            {noformat}
            SELECT ...
            {noformat}
            {noformat}
            
            Regards.
            Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
            • 3. Re: need to get latest record
              AlbertoFaenza
              Hi,
              It looks that Frank and me have posted opposite solution. I'm not sure about your requirements.

              You said you wanted to have latest value of a date field. I would suggest to put some sample data(CREATE TABLE and INSERT statement or a WITH clause with data) with your expected output and explain also what you want to do in case you have NULL values.

              Regards.
              Al