T-SQL Help - Row Counter

  • See sample data below. This is a simple select query, ordered by columns 1, 2, 3.

    I want to add a new (fourth) column that shows a counter in order by "flddate". So BIGSANDDRAW22 would number 1 down to 21 in order of flddate. Chalk3 would number 1, 2 in order by flddate. SHAD02 would number 1,2,3,4 in order of flddate, etc.

    Each time the uniquewell/evendate changes, I need it to start over at 1.

    I would like to do this in a query, and not edit the source table.

    I hope I've explained this well enough. Thank you for your help.

    [edit: Please note that "flddate" sometimes skips days...each flddate is not the immediate next calendar day.]

  • Read about ROW_NUMBER here : ROW_NUMBER .. This is exactly what you need!

  • For starters, here is a sample code

    IF OBJECT_ID('TempDB..#Temp') IS NOT NULL

    DROP TABLE #Temp;

    CREATE TABLE #Temp

    (

    UserName VARCHAR(10),

    EventDt DATETIME,

    filDate DATETIME

    )

    INSERT INTO #Temp

    SELECT 'A' , '2012-01-01' , '2012-01-01 01:00:00'

    UNION ALL SELECT 'A', '2012-01-01' , '2012-01-01 02:00:00'

    UNION ALL SELECT 'A', '2012-01-01' , '2012-01-01 03:00:00'

    UNION ALL SELECT 'A', '2012-01-01' , '2012-01-01 04:00:00'

    UNION ALL SELECT 'A', '2012-01-01' , '2012-01-01 05:00:00'

    UNION ALL SELECT 'B', '2012-01-02' , '2012-01-01 01:00:00'

    UNION ALL SELECT 'B', '2012-01-01' , '2012-01-01 02:00:00'

    UNION ALL SELECT 'C', '2012-01-03' , '2012-01-01 01:00:00'

    UNION ALL SELECT 'C', '2012-01-03' , '2012-01-01 02:00:00'

    UNION ALL SELECT 'C', '2012-01-03' , '2012-01-01 03:00:00'

    UNION ALL SELECT 'C', '2012-01-03' , '2012-01-01 01:00:00'

    UNION ALL SELECT 'A', '2012-01-02' , '2012-01-01 05:00:00'

    UNION ALL SELECT 'D', '2012-01-04' , '2012-01-01 01:00:00'

    SELECT UserName , EventDt , filDate

    , RN = ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY filDate)

    FROM #Temp

    ORDER BY UserName , RN

  • Wow, that was easy! Thank you!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply