Getting Top 1 record from group
-
Hi all, I think my brain has gone into meltdown as I cannot for the life of me see why I cannot get this to work.
Here is an example of my problem
DECLARE @Players TABLE
(
NumId INT IDENTITY(1,1),
MatchReportId INT,
PlayerId INT,
CommentId INT,
ClubId INT,
ReportDate SMALLDATETIME
)
Example data
INSERT INTO @players SELECT 1, 1, 1, 1, '01/01/2000'
INSERT INTO @players SELECT 2, 1, 2, 1, '01/02/2000'
INSERT INTO @players SELECT 3, 1, 3, 2, '01/03/2000'
INSERT INTO @players SELECT 4, 1, 4, 2, '01/04/2000'
INSERT INTO @players SELECT 5, 2, 5, 3, '01/05/2000'
INSERT INTO @players SELECT 6, 2, 6, 3, '01/06/2000'
INSERT INTO @players SELECT 7, 3, 7, 4, '01/07/2000'
INSERT INTO @players SELECT 8, 3, 8, 4, '01/08/2000'
What I then need to do with this data is to get a single record for each playerid that has the most recent date. i.e. I need to return the following records
INSERT INTO @players SELECT 4, 1, 4, 2, '01/04/2000'
INSERT INTO @players SELECT 6, 2, 6, 3, '01/06/2000'
INSERT INTO @players SELECT 8, 3, 8, 4, '01/08/2000'
I am sure that this should be very simple but I think I have gone code blind and just cannot see it, any help greatly apreciated.
-
Try this:
SELECT * FROM @players p WHERE ReportDate = (SELECT MAX(ReportDate) FROM @players q WHERE p.PlayerId = q.PlayerId)
HTH.
-
Thanks Paul, at last I can now take a break!
-
A more advanced version of the top + group by ::
SELECT O.XType
, O.name
FROM dbo.SysObjects O
WHERE ID IN (SELECT TOP 90 PERCENT ID FROM dbo.SysObjects O2 WHERE O2.XType = O.XType order by O2.Name)
ORDER BY O.XType, O.Name
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply