How to disambiguate a sequence in sql?

Question

I have a table of data which represents a series of events that persons do over time, sometimes people do the same thing several times in a row. How to select a result using MS SQL 2008 that shows only disambiguated sequences of those events?

Source data:
Person   Event  Time
1        2      1
1        2      20
1        2      33
2        1      34
1        4      43
1        2      44
2        3      45
1        2      46
1        3      50
1        3      55

Result:
Person   Event
1        2
2        1
1        4
1        2
2        3
1        3

Answer 1

select Person, Event, last(time) from X group by Person, event

Answer 2

I am not sure if I get you right. Maybe you want

SELECT DISTINCT Person, Event FROM Table Where 1 ORDER BY Person;

This selects all your rows, but removes duplicates. So this should yield:

Result:
Person  Event
1       2
1       4
1       3
2       1
2       3

Answer 3

try this:

DECLARE @YourTable table (Person int, Event int, Time int)
SET NOCOUNT ON
INSERT INTO @YourTable VALUES (1, 2 , 1)
INSERT INTO @YourTable VALUES (1, 2 , 20)
INSERT INTO @YourTable VALUES (1, 2 , 33)
INSERT INTO @YourTable VALUES (2, 1 , 34)
INSERT INTO @YourTable VALUES (1, 4 , 43)
INSERT INTO @YourTable VALUES (1, 2 , 44)
INSERT INTO @YourTable VALUES (2, 3 , 45)
INSERT INTO @YourTable VALUES (1, 2 , 46)
INSERT INTO @YourTable VALUES (1, 3 , 50)
INSERT INTO @YourTable VALUES (1, 3 , 55)
SET NOCOUNT OFF

;WITH Ranked AS
(SELECT
     Person,Event,Time
     ,ROW_NUMBER() OVER(PARTITION by Person order by time,Person, Event) AS RowNumber
     FROM @YourTable
 )
SELECT 
    r1.Person,r1.Event 
    FROM Ranked                r1
        LEFT OUTER JOIN Ranked r2 ON r1.RowNumber=r2.RowNumber-1 AND r1.Person=r2.Person 
    WHERE r1.Event!=ISNULL(r2.Event,-999)

OUTPUT:

Person      Event
----------- -----------
1           2
1           4
1           2
1           3
2           1
2           3

(6 row(s) affected)

OP doesn't say which version of SQL Server, so here is the CTE free version for pre SQL Server 2005, same output as above:

SELECT 
    r1.Person,r1.Event 
    FROM (SELECT
              Person,Event,Time
              ,ROW_NUMBER() OVER(PARTITION by Person order by time,Person, Event) AS RowNumber
              FROM @YourTable
         ) r1
        LEFT OUTER JOIN (SELECT
                             Person,Event,Time
                             ,ROW_NUMBER() OVER(PARTITION by Person order by time,Person, Event) AS RowNumber
                             FROM @YourTable
                        ) r2 ON r1.RowNumber=r2.RowNumber-1 AND r1.Person=r2.Person 
    WHERE r1.Event!=ISNULL(r2.Event,-999)

Answer 4

I prefer COUNT <-> GROUP BY over DISTINCT

SELECT Person, Event, COUNT(Time) Amount FROM Table GROUP BY Person, Event ORDER BY COUNT(Time) DESC