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
AndrewHi,
Your query looks good.
Thanks
Hari
SQL Server MVP
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> 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
>|||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
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
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> 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
>|||Andrew,
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
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'. I
f
a username does not have date values in this range then it will not appear i
n
the result.
AMB
"Andrew Webb" wrote:
> Hi
> I'd like to create a query which returns the MAX of a group of dates so lo
ng
> 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
>
>
