"where" clause used after Max()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

"where" clause used after Max()

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-20 : 09:14:02

javaebdon writes "Hi, I got a table Test as follows:

GID LastModifiedDate
----------- --------------------------
1 Jan 1 2004 12:00AM
1 Jan 2 2004 12:00AM
2 Jan 3 2004 12:00AM
13 Jan 2 2004 12:00AM
13 Jan 4 2004 12:00AM

And my statement is :
select distinct GID, max(LastModifiedDate) from Test
where GID=1
go

Do not know why I got result as follows, instead of just rows with GID =1

GID MAXTIME
----------- --------------------------
1 Jan 2 2004 12:00AM
2 Jan 2 2004 12:00AM
13 Jan 2 2004 12:00AM

Thanks"

carrey
Starting Member

22 Posts

Posted - 2004-05-20 : 09:18:13

You need to use a GROUP BY instead of DISTINCT:

select GID, max(LastModifiedDate)
from Test
where GID=1
GROUP BY GID

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-05-20 : 16:53:12

Or, since you already know the GID because you've put it in your WHERE clause, you can leave it out of the field list and since there are no other fields in the list, you can leave off the GROUP BY and just do:

SELECT max(LastModifiedDate)
FROM Test
WHERE GID = 1

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources