Jeff Reese
3/28/2007 5:44:00 PM
Alejandro,
Thanks. I'm getting the following errors:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ';'.
Msg 195, Level 15, State 10, Line 7
'row_number' is not a recognized function name.
--
Jeff
"Alejandro Mesa" wrote:
> Jeff Reese,
>
> See if this helps.
>
> create table dbo.t1 (
> record_id int not null,
> change_type varchar(25) not null,
> change_date datetime
> )
> go
>
> insert into dbo.t1 values(1, 'Created', '3/1/07')
> insert into dbo.t1 values(1, 'Resolved', '3/1/07')
> insert into dbo.t1 values(1, 'Resolved', '3/2/07')
> insert into dbo.t1 values(1, 'Reopened', '3/3/07')
> insert into dbo.t1 values(1, 'Resolved', '3/4/07')
> insert into dbo.t1 values(1, 'Resolved', '3/5/07')
> go
>
> ;with cte
> as
> (
> select
> record_id, change_date, change_type,
> row_number() over(partition by record_id order by change_date) as rn
> from
> dbo.t1
> )
> delete
> b
> from
> cte as a
> inner join
> cte as b
> on a.record_id = b.record_id
> and a.change_type = b.change_type
> and a.rn = b.rn - 1
> go
>
> select *
> from dbo.t1
> order by record_id, change_date
> go
>
> drop table dbo.t1
> go
>
>
> AMB
>
> "Jeff Reese" wrote:
>
> > Alejandro,
> >
> > Yes, I definitely have some typos. Let me correct. Here's a more realistic
> > set of data:
> >
> > Record_ID Change_Type Change_Date
> > 1 Created 3/1/07
> > 1 Resolved 3/1/07
> > 1 Resolved 3/2/07
> > 1 Reopened 3/3/07
> > 1 Resolved 3/4/07
> > 1 Resolved 3/5/07
> >
> > In this scenario... I want to delete Resolved on 3/2 and Resolved on 3/5.
> > The others are valid.
> >
> > Thanks.
> >
> >
> > --
> > Jeff
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > Jeff Reese,
> > >
> > > The sample data is not in synch with the "select" statement you are using to
> > > declare the cursor. I do not see column [change_date] in the sample data and
> > > neither column [Category] when declaring the cursor. Can you explain what you
> > > are trying to do (be more explicit)?
> > >
> > > See if this helps:
> > >
> > > ;with cte
> > > as
> > > (
> > > select
> > > record_id, change_date, change_type,
> > > row_number() over(partition by record_id, change_type order by
> > > change_date) as rn
> > > from
> > > my_table
> > > )
> > > delete cte
> > > where rn > 1;
> > >
> > > Code has not been tested.
> > >
> > > AMB
> > >
> > >
> > > "Jeff Reese" wrote:
> > >
> > > > Hello.
> > > >
> > > > I'm using SQL Server 2005.
> > > >
> > > > What I'm trying to accomplish: Using a cursor to move through records and do
> > > > some data cleanup to remove duplicates
> > > >
> > > > An example:
> > > > Record_ID Category
> > > > 1 Created
> > > > 1 Deleted
> > > > 1 Deleted
> > > >
> > > > In this example, I need to delete the second 'Deleted' row.
> > > >
> > > > The problem: I'm using the delete from <table> where current of cursor and
> > > > it's not working. Does anyone see the problem with this code? I essentially
> > > > use variables to collect values of current and prior rows and compare them to
> > > > see if they're a match. No errors are returned, but my duplicates are still
> > > > there...
> > > >
> > > > Any help is appreciated.
> > > >
> > > >
> > > > declare
> > > > @v_prior_record_id char(10)
> > > > , @v_prior_change_type varchar(20)
> > > > , @v_current_record_id char(10)
> > > > , @v_current_change_type varchar(20)
> > > >
> > > > declare c_cleanup cursor for
> > > > select mt.record_id
> > > > , mt.change_type
> > > > from my_table mt
> > > > order by record_id, change_date
> > > >
> > > > open c_cleanup
> > > > while @@fetch_status = 0
> > > > begin
> > > > fetch next from c_cleanup into @v_current_record_id, @v_current_change_type
> > > >
> > > > if @v_current_record_id = @v_prior_record_id
> > > > and @v_current_change_type = @v_prior_change_type
> > > > delete from my_table
> > > > where current of c_cleanup
> > > >
> > > > set @v_prior_record_id = @v_current_record_id
> > > > set @v_prior_change_type = @v_current_change_type
> > > >
> > > > end
> > > >
> > > >
> > > >
> > > > --
> > > > Jeff