Assigning A Visit Number To Rows In A Database?

Tried searching around with no luck, maybe I'm just not using right the terminology. Anyway, I'm working with a postgresql 8.1 database (redshift) where a sample of the data would

Solution 1:

One (potentially slow) option is to use a correlated subquery:

SELECT  t.UserID, 
        t.Timestamp, 
        t.VisitID,
        (   SELECTCOUNT(*) +1FROM    T T2
            WHERE   T2.UserID = T.UserID
            AND     T2.TimeStamp < T.TimeStamp
        ) VisitNumber
FROM    T;

Example on SQL Fiddle

The easiest (although possibly not viable) solution would be to upgrade to Postgresql 8.4 or later and take advantage of ROW_NUMBER:

SELECT  t.UserID, 
        t.Timestamp, 
        t.VisitID,
        ROW_NUMBER() OVER(PARTITIONBY t.UserID ORDERBY t.Timestamp) AS VisitNumber
FROM    T;

Example on SQL Fiddle

Finally, if the first query is too slow, and upgrading is not an option the following article goes into some detail about a further alternative. I have no idea how this will perform though.

http://www.depesz.com/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/