SELECT Distinct max value! - SQL Server Forums

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

SQL Server Development (2000)

SELECT Distinct max value!

Author

Topic

cornall
Posting Yak Master

148 Posts

Posted - 2008-11-17 : 12:02:09

Hi,

I have a table


serverName, dateStamp,    comment

serverA     10-11-2008    a comment a
serverA     11-11-2008    a comment b
serverA     12-11-2008    a comment c
serverB     10-11-2008    a comment d
serverB     11-11-2008    a comment e
serverB     12-11-2008    a comment f
serverC     10-11-2008    a comment g
serverC     11-11-2008    a comment h
serverC     12-11-2008    a comment i

I want to return the latest comment for each distinct server

e.g.


serverA     12-11-2008    a comment c
serverB     12-11-2008    a comment f
serverC     12-11-2008    a comment i

I can use


SELECT DISTINCT MAX(dateStamp) AS dateStamp, 
		serverName
	FROM myTable
        GROUP BY serverName

That gets me


serverA     12-11-2008 
serverB     12-11-2008   
serverC     12-11-2008

How do I get the latest comment for each distinct server?

Cheers Dave

cornall
Posting Yak Master

148 Posts

Posted - 2008-11-17 : 12:11:35

Opps this should be in the T-SQL area of the forum!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 12:42:31

[code]SELECT t.serverName,
t.dateStamp,
t.comment
FROM yourtable t
INNER JOIN (SELECT serverName,MAX(dateStamp) AS MaxDate
FROM yourtable
GROUP BY serverName)t1
ON t1.serverName=t.serverName
AND t1.MaxDate=t.dateStamp[/code]

cornall
Posting Yak Master

148 Posts

Posted - 2008-11-18 : 03:50:27

Cheers,

Didn't realise I could do a JOIN on my SELECT very useful.

Thanks again.

D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources