6 Replies Latest reply: Feb 7, 2013 5:46 AM by 899401 RSS

    hide/not equla to last row of records

    899401
      Hi,

      I have below mentioned records in mount so whenever i select any mount it should now show the the last row of that mount i.e. test no-3
      any records of mount which will not show last row of test number

      mount     day     org1 test_No org2 org3     org4
      299.00 1     0     1     1     0     7186
      299.00     1     0     2     1     0     7186
      299.00     0     3     3     0     7186
      298.00 0     4 1     0     7186
      298.00     1     0     2     1     0     7186
      298.00     1     1     3     1     0     7186
        • 1. Re: hide/not equla to last row of records
          jeneesh
          You want to hide or show the last row?

          If you want to show only last row, use the below query..
          select mount, day, org1, test_no, org2, org3, org4
          from
          (
            select mount, day, org1, test_no, org2, org3, org4,
                     row_number() 
                          over (partition by mount order by test_no desc) rn
            from your_table t
            --"Use the below filter if you need"
            --where mount = &your_value
          )
          where rn = 1;
          If you want to hide only last row, use the below query..
          select mount, day, org1, test_no, org2, org3, org4
          from
          (
            select mount, day, org1, test_no, org2, org3, org4,
                     row_number() 
                          over (partition by mount order by test_no desc) rn
            from your_table t
            --"Use the below filter if you need"
            --where mount = &your_value
          )
          where rn != 1;
          Edited by: jeneesh on Feb 7, 2013 3:58 PM
          • 2. Top-N Query
            Frank Kulash
            Hi,
            896398 wrote:
            Hi,

            I have below mentioned records in mount so whenever i select any mount it should now show
            Did you mean "it should *not* show"?
            the the last row of that mount i.e. test no-3
            any records of mount which will not show last row of test number

            mount     day     org1 test_No org2 org3     org4
            299.00 1     0     1     1     0     7186
            299.00     1     0     2     1     0     7186
            299.00     0     3     3     0     7186
            298.00 0     4 1     0     7186
            298.00     1     0     2     1     0     7186
            298.00     1     1     3     1     0     7186
            Here's one way to do that:
            WITH     got_r_num     AS
            (
                 SELECT     mount, day, org1, test_no, org2, org3, org4
                 ,     DENSE_RANK () OVER ( PARTITION BY  mount
                                           ORDER BY          test_no     DESC
                                   )   AS r_num
                 FROM    table_x
            )
            SELECT     mount, day, org1, test_no, org2, org3, org4
            FROM     got_r_num
            WHERE     r_num     > 1
            ;
             

            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}
            • 3. Re: hide/not equla to last row of records
              899401
              thanks
              • 4. Re: hide/not equla to last row of records
                899401
                Hi, but test_no required in ascending order only

                thanks
                • 5. Re: hide/not equla to last row of records
                  jeneesh
                  896398 wrote:
                  Hi, but test_no required in ascending order only

                  thanks
                  Not clear, do you face more issues?

                  Please explain..
                  • 6. Re: hide/not equla to last row of records
                    899401
                    over (partition by mount order by test_no desc) rn

                    test_no should be in ascending order only

                    thanks