SELECT Problem - 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

Transact-SQL (2000)

SELECT Problem

Author

Topic

kitkatrobins
Starting Member

3 Posts

Posted - 2005-06-25 : 10:22:53

I have a table as the following

ID | PlayerID | Firstname | Lastname | Version | Deleted

1 1 Something Something 1 0
2 1 Updated Updated 2 0
3 1 UpdatedAgain Some 3 1

I need to be able to get record with the latest version (if deleted return nothing). Can somebody help me?

Regards

kitkatrobins
Starting Member

3 Posts

Posted - 2005-06-25 : 10:25:05

Sorry, based on PlayerID aswell

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-26 : 00:21:32

I'm not sure if you want to get the latest version that is not deleted, or if you want to return nothing if the latest version is deleted. Therefore, I wrote two queries to hopefully satisfy both conditions:

SELECT * FROM YourTable
JOIN
(SELECT PlayerID, MAX(Version) AS MaxVersion
FROM YourTable
WHERE Deleted = 0
GROUP BY PlayerID) AS MaxVer
ON YourTable.PlayerID = MaxVer.PlayerID AND YourTable.Version = MaxVer.MaxVersion

SELECT * FROM YourTable
JOIN
(SELECT PlayerID, Deleted, MAX(Version) AS MaxVersion
FROM YourTable
GROUP BY PlayerID, Deleted) AS MaxVer
ON YourTable.PlayerID = MaxVer.PlayerID AND YourTable.Version = MaxVer.MaxVersion AND MaxVer.Deleted = 0

kitkatrobins
Starting Member

3 Posts

Posted - 2005-06-26 : 16:51:30

awesome - cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources