This discussion is archived
5 Replies Latest reply: Jan 2, 2013 1:29 AM by Karthick_Arp RSS

Need Query

851528 Newbie
Currently Being Moderated
how to fetch the last two inserted rows in each dept.


i wrote the following query but it is taking so much of time but not giving the output.can anyone suggest.

select * from rcpt_tran where rcpt_dt in(
select MAX(rcpt_dt) from rcpt_tran
WHERE rcpt_dt NOT IN (select MAX(rcpt_dt) from rcpt_tran group by msno
) group by msno
union
select MAX(rcpt_dt) from rcpt_tran group by msno)
  • 1. Re: Need Query
    908002 Expert
    Currently Being Moderated
    some thing like this...

    -- thinking msno belongs to department
    select * from (select a.*, row_number() over (partition by msno order by rcpt_Date desc) rn
    from rcpt_tran) where rn in(1,2)
    not tested
  • 2. Re: Need Query
    avish16 Explorer
    Currently Being Moderated
    We dont have access to your database and cannot understand what you intend to do unless we have your table structures and sample datasets.Please provide the same as well what is your desired o/p..
  • 3. Re: Need Query
    Karthick_Arp Guru
    Currently Being Moderated
    848525 wrote:
    how to fetch the last two inserted rows in each dept.


    i wrote the following query but it is taking so much of time but not giving the output.can anyone suggest.

    select * from rcpt_tran where rcpt_dt in(
    select MAX(rcpt_dt) from rcpt_tran
    WHERE rcpt_dt NOT IN (select MAX(rcpt_dt) from rcpt_tran group by msno
    ) group by msno
    union
    select MAX(rcpt_dt) from rcpt_tran group by msno)
    You can use analytic functions like ROW_NUMBER, RANK, DENSE_RANK.

    One example using RANK
    select *
      from (
    select t.*, rank() over(partition by msno order by rcpt_dt desc) rk
      from rcpt_tran t
           )
     where rk <= 2
    Edited by: Karthick_Arp on Jan 2, 2013 1:28 AM
    Added alias t to rcpt_tran
  • 4. Re: Need Query
    851528 Newbie
    Currently Being Moderated
    here t means from where u have taken.
  • 5. Re: Need Query
    Karthick_Arp Guru
    Currently Being Moderated
    848525 wrote:
    here t means from where u have taken.
    I missed th alias t for the table name. I have updated it please check.

Legend

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