CODEWITHSUNDEEP
sqlsql-servergroup-bysql-delete
user4728701
user4728701

Reputation:

How to delete all rows by group except the first and the last one in SQL Server?

I have data like this

Id Name AuthorId 
----------------
1  AAA  2
2  BBB  2
3  CCC  2
4  DDD  3
5  EEE  3

I need a query which will delete all rows by group AuthorId if there are more then 2, except the first and the last one.

For example, in the above data, the second row should be deleted, because, for AuthorId = 2, I have 3 rows, but for AuthorId = 3, nothing will be deleted

Upvotes: 6

Views: 5687

Answers (5)

Thangadurai.B
Thangadurai.B

Reputation: 561

Try this,

Declare @Temp_Data table (id int,name varchar(50),Authorid int)

insert into @Temp_Data values (1,'AAA',2)
insert into @Temp_Data values (2,'BBB',2)
insert into @Temp_Data values (3,'CCC',2)
insert into @Temp_Data values (4,'DDD',3)
insert into @Temp_Data values (5,'EEE',3)

Delete a
from @Temp_Data as a
inner join @Temp_Data as b on a.authorid=b.authorid and b.id > a.id
inner join @Temp_Data as c on a.authorid=c.authorid and c.id < a.id

select * from @Temp_Data

Upvotes: 1

Red Devil
Red Devil

Reputation: 2393

You can try this:

Declare @t table (id int,name varchar(50),Authorid int)

insert into @t values (1,'AAA',2)
insert into @t values (2,'BBB',2)
insert into @t values (3,'CCC',2)
insert into @t values (4,'FFF',2)
insert into @t values (5,'DDD',3)
insert into @t values (6,'EEE',3)
;with cte as
(
select * from (
select *,count(*) over (partition by authorid) cnt from @t
) t
where cnt > 2
)

delete a from cte b join @t a on a.id=b.id where b.id not in (select min(id) from cte group by Authorid) and b.id not in (select max(id) from cte group by Authorid)

select * from @t

Upvotes: 1

Serg
Serg

Reputation: 22811

Row_number() twice and delete non-terminals

delete t
from (
   select *,
      row_number() over(partition by [AuthorId]  order by [Id]) n1,
      row_number() over(partition by [AuthorId]  order by [Id] desc) n2
   from tablename
) t
where n1 > 1 and n2 > 1

Upvotes: 14

forpas
forpas

Reputation: 164099

With EXISTS:

delete t
from tablename t
where 
  exists (
    select 1 from tablename
    where authorid = t.authorid and id > t.id
  )
  and
  exists (
    select 1 from tablename
    where authorid = t.authorid and id < t.id
  )

See the demo.
Results:

Id  Name    AuthorId
1   AAA     2
3   CCC     2
4   DDD     3
5   EEE     3

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You could try using a union for min and max id and NOT in the result for this subquery 

delete from my_table 
where id  NOT  IN  (


    select  min(id) 
    from my_table 
    group by AuthorId 
    union 
    select max(id)
    from my_table 
    group by AuthorId 

)

Upvotes: 1

Related Questions