finding earliest/latest stored date for record

[marco-marco]

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?

[DylanBlitz]

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.

[gizmola]

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

[marco-marco]

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