Help with an aggregate query

[eerich]

I have a query where I'd like to select all records where the entrydate is greater than the latest postdate in the table. I've written the following:

Select name, amount, entrydate, postdate
From Main
Where entrydate > max(postdate)
order by entrdate desc;

I receive an error that an aggregate cant be used in the WHERE clause.

I tried using Group by and 'Having' but since I want all of the records (more than the 4 fields used for the example) to be evaluated against the max postdate as a whole (in my case 7/29/07) it doesnt seem to work. The result set returns every record where the entrydate is greater than the postdate for that record.

Any help is appreciated.

 

[PHV]

You may try this:
SELECT name, amount, entrydate, postdate
FROM Main
WHERE entrydate > (SELECT Max(postdate) FROM Main)
ORDER BY entrydate DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eerich]

PHV, you earn infinite stars.

It worked perfectly!

Thanks.