Jump to content
Updating IPB tonight Read more... ×

Archived

This topic is now archived and is closed to further replies.

marco-marco

finding earliest/latest stored date for record

Recommended Posts

I have many records each with a date assigned that refer to a unique id number .

Is there an easy way to find the earliest and latest date stored for each id number when the earliest date may not be the first id number stored?

Share this post


Link to post
Share on other sites

hmm, if it\'s a date field or datetime field, you could do a select for that id number and order it by the date field and limit it to 1, that would get you the first date, then do the same with the order descending, that would get you the last.

Share this post


Link to post
Share on other sites

While Dylan\'s way would work, the better way is to use the min() and max() summary functions.

 

Although it\'s unclear, you seem to suggest you have a table with at least these columns:

 

Table TableA

------------------------

id | INT

somedate | DATE

... etc

 

 

What\'s confusing is you state you could have multiple rows which have the same id, only the somedate would be different... or in other words, you have \"groups\" of the same id in TableA.

 

If that is true, then this would get you the earliest date for each id

 


SELECT MIN(somedate) as mindate, MAX(somedate) as maxdate FROM TableA  GROUP BY id

Share this post


Link to post
Share on other sites

Thanks all,

 

for gizmola, the tables are in the following form:


UniqueID   Reference       Date              etc

   1           1        2003-09-09

   2           1        2003-08-09

   3           2        2004-01-17

   4           1        2002-12-29



etc etc

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.