Hi

I'd like to create a query which returns the MAX of a group of dates so long
as the number is less than a given date. For example :


SELECT MAX(date), username
FROM mydatatable
WHERE date < '01/01/2005'
GROUP BY username


Will this do what I expect and return the username and date which is the
most recent before 01/01/2005 ?

Thanks

Andrew

Hi,

Your query looks good.

Thanks
Hari
SQL Server MVP

Show quoteHide quote

You could compare these queries and see which one yields the results you
want.  Word problems are tough to solve, usually better to provide specs as
described in http://www.aspfaq.com/5006 .  Also, "date" is a really bad name
for a column.  Not only is it a reserved word, it is also very tough to
decipher it... date of WHAT?  Finally, do not use m/d/y or d/m/y date
formats when hard-coding date strings.  The safest approach here is to use
YYYYMMDD format, then this can't be confused by software or humans.

CREATE TABLE dbo.myDataTable
(
username VARCHAR(32),
eventDate SMALLDATETIME
)
GO

SET NOCOUNT ON
INSERT myDataTable SELECT 'bob','20040101'
INSERT myDataTable SELECT 'bob','20050201'
INSERT myDataTable SELECT 'frank','20040101'
INSERT myDataTable SELECT 'frank','20040725'
GO

SELECT username, MAX(eventDate)
FROM dbo.myDataTable
WHERE eventDate < '20050101'
GROUP BY username

SELECT username, MAX(eventDate)
FROM dbo.myDataTable
GROUP BY username
HAVING MAX(eventDate) < '20050101'
GO

DROP TABLE dbo.myDataTable
GO





Show quoteHide quote

Andrew,

It is correct, but it could be more than one. It will select each username
and the max date for those username with date values less than '20050101'. If
a username does not have date values in this range then it will not appear in
the result.


AMB

Show quoteHide quote

Selecting when Dates in where clause
"If you want a lot of information, one has to use cursors."
How to get variable number of rows on one row.
Returning a value from query
slow Select query specifying daterange using variables.