Well ... If I correctly understood the question, and without looking at performance :
Edit large: I noticed by the comments that
If there are three equal lines in sequence, only one line will remain.
I did not take this into account in the original code. Big Edits to run.
declare @Pedidos as table (Cliente int, Status int, DataHora datetime )
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,0 ,'03-11-2017 18:10:56:500')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,2 ,'03-11-2017 18:10:57:410')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,1 ,'03-11-2017 18:10:54:923')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,3 ,'03-11-2017 18:26:50:513')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,4 ,'03-11-2017 18:27:06:143')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,6 ,'03-11-2017 20:00:01:523')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,18,'03-11-2017 20:10:05:563')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,6 ,'03-11-2017 19:55:26:983')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,6 ,'03-11-2017 20:15:06:563');
1) List all requests:
WITH todosOsPedidosOrdenados as
(
select ROW_NUMBER() OVER(ORDER BY DataHora ASC) as linha , * from @pedidos
)
2) Count the number of duplicates:
, contagem as
(
select COUNT(*) as duplicates, cLIENTE, status FROM todosOsPedidosOrdenados GROUP BY Cliente, Status
)
, duplicados as
(
select * from contagem where duplicates > 1
)
3) Realize what is the request we will want to keep
, pedidoAManter as
(
SELECT
todosOsPedidosOrdenados.Cliente,
todosOsPedidosOrdenados.Status,
min(todosOsPedidosOrdenados.DataHora) as dataHoraAManter
FROM
duplicados LEFT JOIN todosOsPedidosOrdenados
ON todosOsPedidosOrdenados.Cliente = duplicados.Cliente
AND todosOsPedidosOrdenados.Status = duplicados.Status
group by todosOsPedidosOrdenados.Cliente, todosOsPedidosOrdenados.status
)
4) Find out which order we will remove , and choose only the latest (guaranteed by top 1 and order by)
, pedidoARemover as
(
SELECT todosOsPedidosOrdenados.Cliente,
todosOsPedidosOrdenados.Status,
todosOsPedidosOrdenados.DataHora
FROM todosOsPedidosOrdenados LEFT JOIN pedidoAManter
ON todosOsPedidosOrdenados.Cliente = pedidoAManter.Cliente
AND todosOsPedidosOrdenados.Status = pedidoAManter.Status
WHERE
todosOsPedidosOrdenados.DataHora <> pedidoAManter.dataHoraAManter
)
5) Save the result
select * INTO #pedidoARemover_ FROM pedidoARemover;
6) Dealing with delete
delete a
from @Pedidos a
left Join #pedidoARemover_ b
on a.Cliente = b.Cliente
and a.Status = b.Status
AND a.DataHora = b.DataHora
WHERE
b.Cliente IS NOT NULL
7) View the result
SELECT * FROM @Pedidos
order by status asc
8) Throw away the temporary table
DROP TABLE #pedidoARemover_;
Final code (copy-paste for SQL Management Studio should work)
declare @Pedidos as table (Cliente int, Status int, DataHora datetime )
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,0 ,'03-11-2017 18:10:56:500')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,2 ,'03-11-2017 18:10:57:410')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,1 ,'03-11-2017 18:10:54:923')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,3 ,'03-11-2017 18:26:50:513')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,4 ,'03-11-2017 18:27:06:143')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,6 ,'03-11-2017 20:00:01:523')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,18,'03-11-2017 20:10:05:563')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,6 ,'03-11-2017 19:55:26:983')
Insert into @Pedidos (Cliente,Status,DataHora)Values(565510,6 ,'03-11-2017 20:15:06:563');
SELECT ROW_NUMBER() OVER(ORDER BY DataHora ASC) as linha , * from @pedidos;
WITH todosOsPedidosOrdenados as
(
select ROW_NUMBER() OVER(ORDER BY DataHora ASC) as linha , * from @pedidos
)
, contagem as
(
select COUNT(*) as duplicates, cLIENTE, status FROM todosOsPedidosOrdenados GROUP BY Cliente, Status
)
, duplicados as
(
select * from contagem where duplicates > 1
)
, pedidoAManter as
(
SELECT
todosOsPedidosOrdenados.Cliente,
todosOsPedidosOrdenados.Status,
min(todosOsPedidosOrdenados.DataHora) as dataHoraAManter
FROM
duplicados LEFT JOIN todosOsPedidosOrdenados
ON todosOsPedidosOrdenados.Cliente = duplicados.Cliente
AND todosOsPedidosOrdenados.Status = duplicados.Status
group by todosOsPedidosOrdenados.Cliente, todosOsPedidosOrdenados.status
)
, pedidoARemover as
(
SELECT todosOsPedidosOrdenados.Cliente,
todosOsPedidosOrdenados.Status,
todosOsPedidosOrdenados.DataHora
FROM todosOsPedidosOrdenados LEFT JOIN pedidoAManter
ON todosOsPedidosOrdenados.Cliente = pedidoAManter.Cliente
AND todosOsPedidosOrdenados.Status = pedidoAManter.Status
WHERE
todosOsPedidosOrdenados.DataHora <> pedidoAManter.dataHoraAManter
)
select * INTO #pedidoARemover_ FROM pedidoARemover;
delete a
from @Pedidos a
left Join #pedidoARemover_ b
on a.Cliente = b.Cliente
and a.Status = b.Status
AND a.DataHora = b.DataHora
WHERE
b.Cliente IS NOT NULL
SELECT * FROM #pedidoARemover_;
SELECT ROW_NUMBER() OVER(ORDER BY DataHora ASC) as linha , * from @pedidos;
DROP TABLE #pedidoARemover_;
Result Screenshot: