Home » SQL & PL/SQL » SQL & PL/SQL » Retrieving the 2 most recent records from a table by date
Retrieving the 2 most recent records from a table by date [message #215191] Fri, 19 January 2007 10:53 Go to next message
dan_thorman
Messages: 14
Registered: January 2007
Junior Member
i have a table that associates notes with deals ( 1:M deals:notes). These notes have a note_id (pk), deal_id (fk), note_d, and note. I am trying to pull only the 2 most recent notes for a given deal by note_d (the note date). i thought this would do it, but it doesn't:

select n1.deal_note_id, n1.note_d, n1.note, n2.deal_note_id, n2.note_d, n2.note
from nbt_dealnote n1, nbt_dealnote n2
where n1.deal_id = n2.deal_id and
n1.deal_id = 1625 and
n1.note_d = (select max(n3.note_d)
from nbt_dealnote n3
where n1.deal_id = n3.deal_id)
and
n2.note_d = (select max(n4.note_d)
from nbt_dealnote n4
where n2.deal_id = n4.deal_id and n2.deal_note_id != n1.deal_note_id and n4.note_d < n1.note_d)

that only pulls the most recent note. can you all help a newbie out please?
Re: Retrieving the 2 most recent records from a table by date [message #215196 is a reply to message #215191] Fri, 19 January 2007 11:04 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
It looks like you're thinking too difficult here. What if you just order the rows per note, based on note_d and then keep the first two rows?

SELECT *
FROM   (SELECT t.deal_note_id
              ,t.note_d
              ,t.note
        FROM   ntb_dealnote t
        WHERE  t.deal_id = 1625
        ORDER  BY note_d DESC)
WHERE  rownum < 3
Re: Retrieving the 2 most recent records from a table by date [message #215197 is a reply to message #215196] Fri, 19 January 2007 11:42 Go to previous message
dan_thorman
Messages: 14
Registered: January 2007
Junior Member
Genius!! Thanks for your help, that worked perfectly Cool
Previous Topic: Convert a numeric value to INTERVAL YEAR in Oracle8i Enterprise Edition Release 8.
Next Topic: months between
Goto Forum:
  


Current Time: Mon Dec 05 12:36:37 CST 2016

Total time taken to generate the page: 0.05935 seconds