September 3, 2014 at 10:57 am
I have a table with 3 relevant columns
col 1 col 2 col 3
A date/timestamp line counter
A date/timestamp line counter
B date/timestamp line counter
I need to select only the A group from column 1 and sort it ascending by the value in column 2.
The line counter in column 3 needs to be updated and it is sequential based on the timestamp.
So the line counter value for the oldest A timestamp would be 1 and the second oldest would be 2.
Does this make sense? I have no experience with looping, so any help would be appreciated.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
September 3, 2014 at 11:04 am
Today to many row_number questions
declare @sample table(id varchar(1),
dt datetime,
line_counter int)
insert into @sample values('A','01/01/2013',null),('A','01/01/2014',null),('B','01/01/2013',null)
select id,dt,row_number() over (partition by id order by dt asc) as line_counter from @sample
September 3, 2014 at 11:23 am
I should have said this is running on an old SQL Server 2000 box. Will your solution still work?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
September 3, 2014 at 11:36 am
declare @sample table(id varchar(1),
dt datetime,
line_counter int)
insert into @sample values('A','01/01/2013',null),('A','01/01/2014',null),('B','01/01/2013',null)
,('A','01/01/2012',null)
select ID,dt,(select COUNT(*) + 1 from @sample t2 where
t1.id = t2.id
and t1.dt > t2.dt) as line_counter
from @sample t1
order by line_counter
September 3, 2014 at 11:42 am
ROW_NUMBER won't work on SQL Server 2000.
You could use a running total solution and the fastest is the quirky update. You can find different type of solutions in the following article: http://www.sqlservercentral.com/articles/T-SQL/68467/
Try to understand any method that you choose and come back if you have questions.
September 3, 2014 at 4:37 pm
Thanks Luis. I took some code from the link and tweaked it. It seems to be working. For this small fix, a cursor did the job.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply