J

The stage:Create table dev_auto_pesos (
cod_peso tinyint
,cod_temporada tinyint
,cod_variedad_rotulada varchar(10)
,valor_peso_neto tinyint
,peso_neto_inf tinyint
,peso_neto_sup tinyint
,estado tinyint
,fecha_creacion datetime
,fecha_modificacion datetime
);
We insert data:Insert into dev_auto_pesos
( cod_peso
, cod_temporada
, cod_variedad_rotulada
, valor_peso_neto
, peso_neto_inf
, peso_neto_sup
, estado
, fecha_creacion
, fecha_modificacion
)
values
(1,1,'a',1,1,1,1,'20210101','20210101'),
(1,1,'a',1,1,1,1,'20210101','20210101'), -- completamente igual
(1,2,'a',1,1,1,1,'20210101','20210101'),
(1,3,'a',1,1,1,1,'20210101','20210101'),
(1,3,'a',1,1,1,1,'20210101','20210101'), -- completamente igual
(1,3,'a',1,1,1,1,'20210201','20210101'),
(1,1,'b',1,1,1,1,'20210201','20210101'),
(1,1,'b',1,1,1,1,'20210201','20210101 10:08'); -- mínimo cambio
Here the question is What does it mean repeated?.The rows identified as - Completely equalor also the one that is identical to the previous one, except that one of the columns has another hour.Treatments are different:Option1: Totally equalSELECT
ROW_NUMBER() OVER (ORDER BY DP.fecha_creacion desc) as indice,
DP.cod_peso
,DP.cod_temporada
,DP.cod_variedad_rotulada
,DP.valor_peso_neto
,DP.peso_neto_inf
,DP.peso_neto_sup
,DP.estado
,DP.fecha_creacion
,DP.fecha_modificacion
FROM dev_auto_pesos as DP
group by DP.cod_peso
,DP.cod_temporada
,DP.cod_variedad_rotulada
,DP.valor_peso_neto
,DP.peso_neto_inf
,DP.peso_neto_sup
,DP.estado
,DP.fecha_creacion
,DP.fecha_modificacion;
It would also be perfectly valid to use distinct from a cte or derivative board.with cte as (
Select distinct
DP.cod_peso
,DP.cod_temporada
,DP.cod_variedad_rotulada
,DP.valor_peso_neto
,DP.peso_neto_inf
,DP.peso_neto_sup
,DP.estado
,DP.fecha_creacion
,DP.fecha_modificacion
FROM dev_auto_pesos as DP
)
SELECT
ROW_NUMBER() OVER (ORDER BY fecha_creacion desc) as indice,
cod_peso
,cod_temporada
,cod_variedad_rotulada
,valor_peso_neto
,peso_neto_inf
,peso_neto_sup
,estado
,fecha_creacion
,fecha_modificacion
FROM cte;
Option 2: Almost equalWhen a column has similar values in several rows, we must use a grouping function. In the case of the example I will choose the max function to return the greater date of the ones that are, and so I remove the record that is about 00:00, staying with the 10:08SELECT
ROW_NUMBER() OVER (ORDER BY DP.fecha_creacion desc) as indice,
DP.cod_peso
,DP.cod_temporada
,DP.cod_variedad_rotulada
,DP.valor_peso_neto
,DP.peso_neto_inf
,DP.peso_neto_sup
,DP.estado
,DP.fecha_creacion
,max(DP.fecha_modificacion)
FROM dev_auto_pesos as DP
group by DP.cod_peso
,DP.cod_temporada
,DP.cod_variedad_rotulada
,DP.valor_peso_neto
,DP.peso_neto_inf
,DP.peso_neto_sup
,DP.estado
,DP.fecha_creacion;
Now we no longer have 6 rows, but 5, because of which it was almost the same, compared to its "brother", we have obtained the one that has the date of major modification as value to attach both rows in one. https://sqlperformance.com/2017/01/t-sql-queries/surprises-assumptions-group-by-distinct https://javifer2.wordpress.com/2019/10/04/group-by-quizas-la-clausula-mas-conflictiva/