max day in the month record

[iori]

hi all,
i want to get the record that has the highest day in the month selected

for example here is a sample table:

id | date
1 | 8/31/06
1 | 8/12/06

i want to be select ONLY 8/31/06 if someone is searching this table in the month of august(8th month),

here is what i tried:
SELECT id,date, max( day(date)) as maxday from fz_dates where month(date)=8 group by id,date;

i m missing something here as i get this result:
id| date | maxday
1 | 8/31/06 | 31
1 | 8/12/06 | 12

ur help will be appreciated

 

[RV]

Your query will look at the max date per row retrieved hence your result.
Try this:

SELECT id,date, max( day(date)) as maxday
from fz_dates
where month(date)=8
and date =
(
SELECT Max(Date)
FROM fz_dates
)

Rename your column date as date is a reserved word in Access and should not be used for object names.

RV

 

[iori]

thanks alot rv..that works fine