SELECT with repeated field

Question

I’m having trouble executing an SQL query.

The consultation is:

SELECT DISTINCT CHAVE_FATO,COD_FILIAL,COD_DOCTO,NUM_DOCTO,DATA,VALOR_TOTAL,VALOR_LIQUIDO,COD_CLI_FOR,NOME_CLI_FOR,COD_VEND_COMP,NOME_VEND_COMP,COD_PRODUTO,DESC_PRODUTO_EST,NOME_SECAO,QTDE_UND,QTDE_PRI,QTDE_AUX,VALOR_UNITARIO,VALOR_TOTAL_ITEM 
FROM [SATK].[dbo].entrada 
WHERE DATA > '2018-01-01 00:00:00' 
ORDER BY NUM_DOCTO ASC

Does not work, the return keeps showing repeated values in the NUM_DOCTO column.

I need to eliminate it, but I’m not getting it.

Answer 1

Complementing the response with group by, you need to indicate in repeated case, which will be the chosen record to be displayed. Can be with MIN (smaller), MAX (larger), SUM (sum), AVG (average), COUNT (quantity).

An example would be:

SELECT MAX(CHAVE_FATO),MIN(COD_FILIAL),MAX(COD_DOCTO),NUM_DOCTO,MIN(DATA),MAX(VALOR_TOTAL),MAX(VALOR_LIQUIDO),MAX(COD_CLI_FOR),MAX(NOME_CLI_FOR),MAX(COD_VEND_COMP),MAX(NOME_VEND_COMP),MAX(COD_PRODUTO),MAX(DESC_PRODUTO_EST),MAX(NOME_SECAO),MAX(QTDE_UND),MAX(QTDE_PRI),MAX(QTDE_AUX),MAX(VALOR_UNITARIO),MAX(VALOR_TOTAL_ITEM) 
FROM [SATK].[dbo].entrada 
WHERE DATA > '2018-01-01 00:00:00'
GROUP BY NUM_DOCTO
ORDER BY NUM_DOCTO ASC

But stay tuned, in this case will be displayed only one line. If you have more than one product linked to this document, will come in the result only one respecting the aggregation function (min, max, sum...).

Answer 2

Use GROUP BY

SELECT DISTINCT CHAVE_FATO,COD_FILIAL,COD_DOCTO,NUM_DOCTO,DATA,VALOR_TOTAL,VALOR_LIQUIDO,COD_CLI_FOR,NOME_CLI_FOR,COD_VEND_COMP,NOME_VEND_COMP,COD_PRODUTO,DESC_PRODUTO_EST,NOME_SECAO,QTDE_UND,QTDE_PRI,QTDE_AUX,VALOR_UNITARIO,VALOR_TOTAL_ITEM 
FROM [SATK].[dbo].entrada 
WHERE DATA > '2018-01-01 00:00:00'
GROUP BY NUM_DOCTO
ORDER BY NUM_DOCTO ASC

More information on: https://www.w3schools.com/sql/sql_groupby.asp