Jump to content


Photo

finding earliest/latest stored date for record


  • Please log in to reply
3 replies to this topic

#1 marco-marco

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

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

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

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