Jump to content


finding earliest/latest stored date for record

  • Please log in to reply
3 replies to this topic

#1 marco-marco

  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 23 December 2003 - 11:45 PM

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?

#2 DylanBlitz

  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 24 December 2003 - 12:17 AM

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.

#3 gizmola

  • Administrators
  • Advanced Member
  • 4,667 posts
  • LocationLos Angeles, CA USA

Posted 24 December 2003 - 02:20 AM

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

#4 marco-marco

  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 24 December 2003 - 09:13 AM

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-29etc etc

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users