This discussion is archived
6 Replies Latest reply: Feb 7, 2013 3:46 AM by 899401 RSS

hide/not equla to last row of records

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

    thanks
  • 5. Re: hide/not equla to last row of records
    jeneesh Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    over (partition by mount order by test_no desc) rn

    test_no should be in ascending order only

    thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points