SQL delete all consecutive records that have the same value, leaving only first and last

Question

i have a situation where i need to delete consecutive records that share the same field value ("Failed to notify Customer") , however i need to leave the first and last instance

sample data

date             type    log
20/11/2014 09:05 System, Order Added
20/11/2014 09:18 Mark,   Invoice Printed
20/11/2014 10:00 System, Failed to notify Customer
20/11/2014 10:05 System, Failed to notify Customer
20/11/2014 10:10 System, Failed to notify Customer
20/11/2014 10:15 System, Failed to notify Customer
20/11/2014 10:20 System, Failed to notify Customer
20/11/2014 12:05 System, Order Completed

resulting output

date             type    log
20/11/2014 09:05 System, Order Added
20/11/2014 09:18 Mark,   Invoice Printed
20/11/2014 10:00 System, Failed to notify Customer
20/11/2014 10:20 System, Failed to notify Customer
20/11/2014 12:05 System, Order Completed

Is there some way of formulating an sql server query to achieve this? for the life of me i cant get my head around how i would approach this

Answer 1

Try this one:

Sample data :

use tempdb

create table temp(
    [date]  datetime,
    type    varchar(100),
    [log]   varchar(100)
)
insert into temp values
('11/20/2014 09:05', 'System', 'Order Added'),
('11/20/2014 09:18', 'Mark', 'Invoice Printed'),
('11/20/2014 10:00', 'System', 'Failed to notify Customer'),
('11/20/2014 10:05', 'System', 'Failed to notify Customer'),
('11/20/2014 10:10', 'System', 'Failed to notify Customer'),
('11/20/2014 10:15', 'System', 'Failed to notify Customer'),
('11/20/2014 10:20', 'System', 'Failed to notify Customer'),
('11/20/2014 12:05', 'System', 'Order Completed');

Solution using ROW_NUMBER() :

with cte as(
    select
        *,
        rn = row_number() over(partition by log order by [date]),
        cc = count(*) over(partition by log)
    from temp
    where
        log = 'Failed to notify Customer'
)
delete
from cte
where
    rn > 1 and rn < cc

select * from temp
drop table temp

Answer 2

select * from table-name where  log='Failed to notify Customer' 
group by log having date>min(date) and date<max(date);

This will select the required rows and delete these rows.

P.S - Syntax may be wrong as I didn't execute and check.

Answer 3

select table that contains the max(date) for each customer and union that with one that contains the min(date) for each customer. Then delete from table where not in your union. Something like this (untested)

DELETE FROM Table
WHERE NOT EXISTS
(SELECT Max(Date), Type, Log
FROM Table
WHERE type = 'System' AND log = 'Failed to notify customer'
GROUP BY Type, Log
UNION ALL
SELECT Min(Date), Type, Log
FROM Table
WHERE type = 'System' AND log = 'Failed to notify customer'
GROUP BY Type, Log)