Maximum value of dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In using SQL in access 2000
In a table contains two field: serial_num , date,
for example
serial_num date
1 2004/1/1
2 2004/1/1
1 2004/1/2
When I type
Select serial_num max(date) As Max_date group by serial_num.
Error occurs
I can't get the record which has maximum date.
How could I do it?
Thx
 
Try somehthing along the following lines

SELECT TOP 1 [date]
FROM <your table name>
ORDER BY [date] DESC;

Hope That Helps
Gerald Stanley MCSD
 
If you want to select the max. date of the whole Table, simply:

SELECT Max([date])
FROM [YourTable]

If you want to select the max. date for *each* serial number:

SELECT [serial_num], max([date]) As Max_date
FROM [YourTable]
GROUP BY [serial_num]

"date" is a bad Field name since it is a reserved word for the inbuilt
function Date(). Suggest you change the Field name to something else or if
you can't, make sure you *always* enclose date in square brackets like
[date] to avoid ambiguity that can lead to hard-to-find errors.
 
Back
Top