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 NULLDROP 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