I have data in which I would like to count the rounds of a game, I got close but when there are subsequent records by the same player then I don't want to count it, for example:
DECLARE @Scores table ( Player varchar(10), Score varchar(20), DateEntered datetime ) INSERT INTO @Scores (Player, Score, DateEntered) VALUES ('Bob', 5, '2019-05-10 14:00:00'), ('Tim', 2, '2019-05-10 14:00:13'), ('Jim', 1, '2019-05-10 14:00:24'), ('Jim', 2, '2019-05-10 14:00:29'), ('Bob', 8, '2019-05-10 14:00:45'), ('Tim', 3, '2019-05-10 14:00:49'), ('Jim', 5, '2019-05-10 14:00:55')
select row_number() over (partition by Player order by DateEntered) round, Player, Score
from @Scores order by DateEntered
This produces the following:
|round|Player|Score| |1|Bob|5| |1|Tim|2| |1|Jim|1| |2|Jim|2| |2|Bob|8| |2|Tim|3| |3|Jim|5|
I want row 4 to be |1|Jim|2| and row 7 to be |2|Jim|5|
Any ideas on how I can accomplish this?