Trouble selecting the most recent value

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

September 7th, 2007, 04:20 PM

Winelover

Registered User

Join Date: Sep 2007

Posts: 2

Time spent in forums: 17 m 27 sec
Reputation Power: 0

Trouble selecting the most recent value

Have a table like this (sorry for the formatting:
ID1 ID2 DATE ID2Value
1 12 1/1/2006 6
1 24 1/1/2006 400
1 24 6/1/2006 410
1 36 6/1/2006 100
2 12 1/1/2007 7
2 24 6/1/2007 350
2 24 7/1/2007 360
I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:
1 24 6/1/2006 410
2 24 7/1/2007 360

I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first. Any direction please? All suggestions appreaciated.

Many thanks,
Winelover

September 7th, 2007, 07:56 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 681

Time spent in forums: 3 Days 14 h 23 m 2 sec
Reputation Power: 12

Quote:

Originally Posted by Winelover

Have a table like this (sorry for the formatting:
ID1 ID2 DATE ID2Value
1 12 1/1/2006 6
1 24 1/1/2006 400
1 24 6/1/2006 410
1 36 6/1/2006 100
2 12 1/1/2007 7
2 24 6/1/2007 350
2 24 7/1/2007 360
I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:
1 24 6/1/2006 410
2 24 7/1/2007 360

I have worked and worked on this and I am st ill stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first. Any direction please? All suggestions appreaciated.

Many thanks,
Winelover

Try something like:

SQL Code:

Original - SQL Code
SELECT A.ID1, A.ID2, A.DATE, A.ID2Value FROM MYTABLE A WHERE (A.ID2 = 24) AND A.DATE = ( SELECT MAX(B.DATE) FROM MYTABLE B WHERE (B.ID2 = 24) AND (B.ID1 = A.ID1) )

 
SELECT A.ID1, A.ID2, A.DATE, A.ID2Value
FROM MYTABLE A
WHERE (A.ID2 = 24)
AND A.DATE = (
      SELECT MAX(B.DATE)
      FROM MYTABLE B
      WHERE (B.ID2 = 24)
      AND (B.ID1 = A.ID1) )

Clive.

September 14th, 2007, 02:47 PM

Winelover

Registered User

Join Date: Sep 2007

Posts: 2

Time spent in forums: 17 m 27 sec
Reputation Power: 0

Thanks - that helped my thought process. In that form, took too long with my gigantic tables, but helped get me where I needed to go.

Appreciate the help.

April 16th, 2008, 09:33 AM

DrewSmith70

Registered User

Join Date: Apr 2008

Posts: 1

Time spent in forums: 31 m 58 sec
Reputation Power: 0

This is faster:

SQL Code:

Original - SQL Code
SELECT * FROM( SELECT A.ID1, A.ID2, A.DATE, A.ID2Value, max(A.DATE) over (partition by A.ID2, A.ID1) max_bdate FROM MYTABLE A WHERE A.ID2 = 24) WHERE DATE = max_bdate

 SELECT * FROM(
     SELECT A.ID1, A.ID2, A.DATE, A.ID2Value, MAX(A.DATE) over (partition BY A.ID2, A.ID1) max_bdate
        FROM MYTABLE A
       WHERE A.ID2 = 24)
  WHERE DATE = max_bdate

Last edited by DrewSmith70 : April 16th, 2008 at 09:35 AM. Reason: Added message

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: