Select the latest Timestamp by non unique record

[ETOMBS]

Im haveing a problem extracting the latest timestamp from a table where the only thing that may differentiate records is the timestamp.

Name result timestamp
gary busey 1 9/1/2010 14:14
gary busey 1 9/2/2010 14:14
Darth Vader 1 9/1/2010 14:14
Darth Vader 1 9/5/2010 14:14
gary busey 2 9/1/2010 14:14
gary busey 1 9/3/2010 14:14


I need to pull only 1 record from this table where the result=1 and the timestamp is the latest timestamp by individual name.

My results should look like this:

Name result timestamp
Darth Vader 1 9/5/2010 14:14
gary busey 1 9/3/2010 14:14


Any help would be appreciated

 

[TheBugSlayer]

Hi ETOMBS,

Add a TOP 1 clause to your query like so:

SELECT TOP 1 [Name], Result, TimeStamp 
FROM YourTable ORDER BY TimeStamp DESC

Good luck.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RiverGuy]

I don't think he is asking for a single result. I think he's asking for the latest result by name and result = 1.

SELECT a.*
FROM YourTable

INNER JOIN

(SELECT [Name], MAX([Timestamp]) AS MaxStamp
FROM YourTable
WHERE Result = 1
GROUP BY [Name]) b

ON a.[Name] = b.[Name]
AND a.[Timestamp] = b.MaxStamp

 

[TheBugSlayer]

RiverGuy, you' re right. He has two different names in the result set he is showing.

This works too and may be a little simpler for you:

SELECT [Name], MAX([Timestamp])
FROM YourTable WHERE Result = 1
GROUP BY [Name]

Good luck.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[Rhys666]

Depending on your version of SQL Server you can also use CTE's and RANK(). I.E., (untested),

WITH Ranked_CTE
AS
(
	SELECT
		Name
		,Result
		,Timestamp
		,RANK() OVER
		(
			PARTITION BY
				Name
			ORDER BY
				Result		ASC
				,Timestamp	DESC
		) AS 'Ranking'
	FROM
		YourTableName
)
SELECT
	*
FROM
	Ranked_CTE
WHERE
	Ranking = 1;

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett