SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting the last set of records based on a Date Field

thinkingeyeApr 28 2011 — edited Apr 28 2011

Hi All
i have the followings data set in a table

TRACK_ID_GRP	TRACK_ID_NBR	TRACK_SAMPLE_ID	START_DATE
			
970150	                 129700104071	64260	                   8/6/2002
970150	                 229700101893	64261	                   8/6/2002
970150	                149700101893	64262	                   8/6/2002
970150	                  97015011	79252	                   9/19/2005
970150	                  97015023	79255	                   9/19/2005
970150	                  97015013	79253	                    9/19/2005
970150	                  97015021	79254	                   9/19/2005
970040	                 129900105213	56155	                    9/26/2000
970040	                            1101	29841	                   6/9/1998
970040	                  97004023	143349	                   10/28/2008
970040	                         2101	29842	                   6/9/1998
970040	                   97004021	143348	                   10/28/2008
970040	                  97004011	143346	                   10/28/2008
970040	                 149700101903	64196	                  7/16/2002
970040	                         2301	29844	                 6/9/1998
970040	                    97004013	143347	                    10/28/2008
970040	                             1301	  29843	                   6/9/1998

I need to get only those records with the latest date and i'm using the following query

SELECT t.track_id_grp, t.track_id_nbr, t.track_sample_id,
       MAX (t.start_date) OVER (PARTITION BY t.track_id_grp) AS start_date
  FROM track_table t
 WHERE t.track_id_grp IN ('970150', '970040')

And i'm getting the following dataset

TRACK_ID_GRP	TRACK_ID_NBR	TRACK_SAMPLE_ID	START_DATE
			
970040	                 1.299E+11	56155	10/28/2008
970040	                 1101	                29841	10/28/2008
970040	                 97004023	                143349	10/28/2008
970040	                 2101	                29842	10/28/2008
970040	                 97004021	                143348	10/28/2008
970040	                 97004011	                143346	10/28/2008
970040	                 1.497E+11	64196	10/28/2008
970040	                 2301	                29844	10/28/2008
970040	                 97004013	                143347	10/28/2008
970040	                 1301	                29843	10/28/2008
970150	                 1.297E+11	64260	9/19/2005
970150	                 97015011	                79252	9/19/2005
970150	                 97015021	                79254	9/19/2005
970150	                 2.297E+11	64261	9/19/2005
970150	                 97015013	                79253	9/19/2005
970150	                 97015023	                79255	9/19/2005
970150	                 149700101903	64262	9/19/2005

And i was expecting the following dataset

TRACK_ID_GRP	TRACK_ID_NBR	TRACK_SAMPLE_ID	START_DATE
970150	                 97015011	                        79252	9/19/2005
970150	                 97015023	                        79255	9/19/2005
970150	                 97015013	                        79253	9/19/2005
970150	                 97015021	                        79254	9/19/2005
970040	                 97004023	                        143349	10/28/2008
970040	                 97004021	                        143348	10/28/2008
970040	                 97004011	                        143346	10/28/2008
970040	                 97004013	                        143347	10/28/2008

I was expecting to get only those records with the latest date, instead i'm getting all the records with the max date in it, please need help or advice.

Thanks

This post has been answered by Boneist on Apr 28 2011

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on May 26 2011

Added on Apr 28 2011

6 comments

167 views