1
I have the following query:
SELECT finpag.cod_forn
,sicforn.nome
,finpag.data_pagto
,finempe.cod_reduzido
,finpag.num_empenho
,finpag.ano_empenho
,num_liquid
,ano_liquid
,valor_transacao
FROM finpag
INNER JOIN sicforn on
sicforn.cod_forn = finpag.cod_forn
INNER JOIN finempe on
(finempe.num_empenho = finpag.num_empenho)
WHERE finpag.data_pagto between '1-1-1997' and '31-12-1997' and finpag.cod_forn = 1840 and finpag.ano_empenho = 97
ORDER BY finpag.num_liquid
That returns to me the following query:
cod_forn/nome/cod_reduzido/num_empenho/ano_empenho/num_liquid/ano_liquid/valor_transacao
1840 LUC'S ELETRODOMESTICOS LTDA 286 1473 97 3554 97 734
1840 LUC'S ELETRODOMESTICOS LTDA 3 1473 97 3554 97 734
1840 LUC'S ELETRODOMESTICOS LTDA 2 1661 97 3671 97 649
1840 LUC'S ELETRODOMESTICOS LTDA 370 1661 97 3671 97 649
1840 LUC'S ELETRODOMESTICOS LTDA 370 1661 97 3903 97 85
1840 LUC'S ELETRODOMESTICOS LTDA 2 1661 97 3903 97 85
1840 LUC'S ELETRODOMESTICOS LTDA 39 1712 97 3942 97 649
1840 LUC'S ELETRODOMESTICOS LTDA 3 1712 97 3942 97 649
1840 LUC'S ELETRODOMESTICOS LTDA 3 1712 97 4143 97 85
1840 LUC'S ELETRODOMESTICOS LTDA 39 1712 97 4143 97 85
1840 LUC'S ELETRODOMESTICOS LTDA 61 1889 97 3894 97 64
1840 LUC'S ELETRODOMESTICOS LTDA 348 1889 97 3894 97 64
1840 LUC'S ELETRODOMESTICOS LTDA 136 2165 97 4571 97 176
1840 LUC'S ELETRODOMESTICOS LTDA 10 2165 97 4571 97 176
1840 LUC'S ELETRODOMESTICOS LTDA 58 2010 97 4331 97 562
1840 LUC'S ELETRODOMESTICOS LTDA 61 2010 97 4331 97 562
1840 LUC'S ELETRODOMESTICOS LTDA 53 2286 97 5111 97 70
1840 LUC'S ELETRODOMESTICOS LTDA 264 2286 97 5111 97 70
1840 LUC'S ELETRODOMESTICOS LTDA 285 1919 97 5714 97 734
1840 LUC'S ELETRODOMESTICOS LTDA 64 1919 97 5714 97 734
1840 LUC'S ELETRODOMESTICOS LTDA 17 2534 97 5277 97 78
1840 LUC'S ELETRODOMESTICOS LTDA 78 2534 97 5277 97 78
1840 LUC'S ELETRODOMESTICOS LTDA 25 2928 97 6612 97 1080
1840 LUC'S ELETRODOMESTICOS LTDA 227 2928 97 6612 97 1080
1840 LUC'S ELETRODOMESTICOS LTDA 17 2773 97 6904 97 130,5
1840 LUC'S ELETRODOMESTICOS LTDA 10 2773 97 6904 97 130,5
1840 LUC'S ELETRODOMESTICOS LTDA 389 2513 97 6621 97 575
1840 LUC'S ELETRODOMESTICOS LTDA 348 2513 97 6621 97 575
1840 LUC'S ELETRODOMESTICOS LTDA 109 2965 97 6762 97 549
1840 LUC'S ELETRODOMESTICOS LTDA 53 2965 97 6762 97 549
1840 LUC'S ELETRODOMESTICOS LTDA 12 2994 97 6763 97 549
1840 LUC'S ELETRODOMESTICOS LTDA 188 2994 97 6763 97 549
1840 LUC'S ELETRODOMESTICOS LTDA 10 2773 97 6960 97 156,6
1840 LUC'S ELETRODOMESTICOS LTDA 17 2773 97 6960 97 156,6
1840 LUC'S ELETRODOMESTICOS LTDA 49 2657 97 6912 97 1500
1840 LUC'S ELETRODOMESTICOS LTDA 31 2657 97 6912 97 1500
1840 LUC'S ELETRODOMESTICOS LTDA 264 3630 97 7698 97 249
1840 LUC'S ELETRODOMESTICOS LTDA 3 3630 97 7698 97 249
1840 LUC'S ELETRODOMESTICOS LTDA 188 2994 97 7198 97 1389
1840 LUC'S ELETRODOMESTICOS LTDA 12 2994 97 7198 97 1389
I need to remove duplicates, as could be done (without using aggregation function)?
if I understand correctly you have the expected return (conditions); then it would only be the case to control the fields you select, no?! You are bringing in
finempe.cod_reduzido
, simply remove it (and other fields from the tablefinempe
be there)– rLinhares
the data are duplicated precisely because of finempe.cod_reduced (there is no cod_column reduced in finpag), as they are different in the table finempe and finpag, so I would like to select only the table finpag
– V.Avancini
Your question is somewhat confused, but as far as I understand it, add in the JOIN condition between the FINEMPE and FINPAG tables the cod_reduced field. {INNER JOIN FINEMPE ON (FINEMPE.NUM_EMPENHO = FINPAG.NUM_EMPENHO AND FINEMPE.COD_REDUZIDO = FINPAG.COD_REDUZIDO)}
– Andrey
@Andrey there is no cod_reduced column in finpag, if you look at the query I look for it from finempe
– V.Avancini
Then place the fields in a GROUP BY and make a COD_REDUZIDO MAX.
– Andrey
@Andrey you could exemplify in a reply? would facilitate my understanding
– V.Avancini