Home » SQL & PL/SQL » SQL & PL/SQL » SQL select
SQL select [message #192533] Tue, 12 September 2006 14:51 Go to next message
liz82much
Messages: 1
Registered: September 2006
Junior Member
I need to select the most recent record within a date range for each combination of two ids. In other words, given the folowing table:

ID1 ID2 MyNum MyDate
1005 27 100 9/12/06 13:00
1001 21 500 9/12/06 13:00
1005 27 300 9/12/06 12:00
1004 21 200 9/12/06 12:00
1001 11 1000 9/12/06 12:00
1001 27 2000 9/12/06 12:00
1001 21 3000 9/12/06 12:00
1005 27 100 8/19/06 13:00


I need the records for the most recent MyDate in the 8hours ending with 9/12/06 13:00 for each ID1 and ID2 pair. Going back to the table, here are what the results should be:

ID1 ID2 MyNum MyDate
1005 27 100 9/12/06 13:00 YES - I Need
1001 21 500 9/12/06 13:00 YES - I Need
1005 27 300 9/12/06 12:00 NO - there's a more recent record
1004 21 200 9/12/06 12:00 YES - I Need
1001 11 1000 9/12/06 12:00 YES - I Need
1001 27 2000 9/12/06 12:00 YES - I Need
1001 21 3000 9/12/06 12:00 NO - there's a more recent record
1005 27 100 8/19/06 13:00 NO - not in the date range


The following select statement gets me the most 13:00 submissions (ie. the 1st 2 records) but not the others.

SELECT ID1, ID2, MyDate, MyNum FROM T
WHERE (MyDate = (
SELECT MAX(MyDate) FROM T WHERE
MyDate >= TO_DATE('09/12/2006 05:00','MM-DD-YYYY HH24:MI') AND
MyDate <= TO_DATE('09/12/2006 13:00','MM-DD-YYYY HH24:MI')))


Any suggestions? Thank you.
Re: SQL select [message #192537 is a reply to message #192533] Tue, 12 September 2006 15:44 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
It would have helped if you could have provided the DDL here.

sql>select id1, id2, mynum, mydate
  2    from (select t.*, row_number() over (partition by id1, id2 order by mydate desc) r
  3            from t
  4           where mydate between to_date('09/12/2006 05:00','MM-DD-YYYY HH24:MI') and 
  5                                to_date('09/12/2006 13:00','MM-DD-YYYY HH24:MI')) 
  6   where rn = 1
  7   order by id1, id2;

      ID1       ID2     MYNUM MYDATE
--------- --------- --------- ---------------------
     1001        11      1000 09/12/2006 12:00:00pm
     1001        21       500 09/12/2006 01:00:00pm
     1001        27      2000 09/12/2006 12:00:00pm
     1004        21       200 09/12/2006 12:00:00pm
     1005        27       100 09/12/2006 01:00:00pm

5 rows selected.
Re: SQL select [message #192659 is a reply to message #192537] Wed, 13 September 2006 03:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or
select distinct t.id1,t.id2, first_value(mydate) over (partition by id1, id2 order by mydate desc) r
from   t
where  mydate between to_date('09/12/2006 05:00','MM-DD-YYYY HH24:MI') 
              and     to_date('09/12/2006 13:00','MM-DD-YYYY HH24:MI')
Re: SQL select [message #192794 is a reply to message #192659] Wed, 13 September 2006 11:32 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
We need to get the mynum column in there though...
Re: SQL select [message #192892 is a reply to message #192794] Thu, 14 September 2006 02:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
True.
I think this'll do it, but I've dropped my test data and can't be bothered to recreate it.

select distinct t.id1
      ,t.id2
      ,first_value(mynum)  over (partition by id1, id2 order by mydate desc) mynum
      ,first_value(mydate) over (partition by id1, id2 order by mydate desc) mydate
from   t
where  mydate between to_date('09/12/2006 05:00','MM-DD-YYYY HH24:MI') 
              and     to_date('09/12/2006 13:00','MM-DD-YYYY HH24:MI')
Re: SQL select [message #193045 is a reply to message #192892] Thu, 14 September 2006 12:06 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Yep, looks good.

This was a good reminder to me about FIRST_VALUE - a very useful analytical function indeed.
Previous Topic: packages
Next Topic: Column Heading in The Output
Goto Forum:
  


Current Time: Sat Dec 10 16:33:27 CST 2016

Total time taken to generate the page: 0.03993 seconds