ajliaks


Hi guys,

need some help here please...

The code below shows 4 rows.

The first two rows are almost identical, but the two of them exists in the same table as different rows.

Row number 1 is also related to Row number 3 and Row number 2 is also related to Row number 4

The problem is that I have to use only one of then (Rows number 1 or 2) togheter with row 3 & 4.

I thought using GROUP BY RECEIPTJURNALMATCH.JURNALTRANSID, but getting error.

Thanks in advance,

Aldo.

Code Snippet

SELECT

RECEIPTJURNALMATCH.JURNALTRANSID AS 'R.JURNALTRANSID',

RECEIPTJURNALMATCH.MATCHNUM AS 'R.MATCHNUM',

JURNALTRANSMOVES.ACCOUNTKEY AS 'J.ACCOUNTKEY',

JURNALTRANSMOVES.SUF AS 'J.TOTAL',

STOCK.REMARKS AS 'S.REMARKS'

FROM

RECEIPTJURNALMATCH

INNER JOIN JURNALTRANSMOVES ON RECEIPTJURNALMATCH.JURNALTRANSID = JURNALTRANSMOVES.ID

LEFT OUTER JOIN STOCK ON RECEIPTJURNALMATCH.STOCKID = STOCK.ID

WHERE

JURNALTRANSMOVES.ACCOUNTKEY IN ('123456')

Below the results:

R.JURNALTRANSID R.MATCHNUM J.ACCOUNTKEY J.TOTAL S.REMARKS

89634 16702 123456 1155 : ;5752
89634 16703 123456 1155 : ;5752
89637 16702 123456 400 NULL
89639 16703 123456 155 NULL





Re: TSQL - Avoid duplicated rows - using distinct / Group by

ajliaks


The code below works but... is there any more efective solution

Thanks,

Aldo.

Code Snippet

SELECT

JURNALTRANSMOVES.ACCOUNTKEY AS 'J.ACCOUNTKEY',

JURNALTRANSMOVES.SUF AS 'J.SUF',

STOCK.REMARKS AS 'S.REMARKS'

FROM

RECEIPTJURNALMATCH

INNER JOIN JURNALTRANSMOVES ON RECEIPTJURNALMATCH.JURNALTRANSID = JURNALTRANSMOVES.ID

LEFT OUTER JOIN STOCK ON RECEIPTJURNALMATCH.STOCKID = STOCK.ID

WHERE

JURNALTRANSMOVES.ACCOUNTKEY IN ('123456')

GROUP BY

RECEIPTJURNALMATCH.JURNALTRANSID,

JURNALTRANSMOVES.ACCOUNTKEY,

JURNALTRANSMOVES.SUF,

STOCK.REMARKS

The results are:

J.ACCOUNTKEY J.SUF S.REMARKS'

123456 1155 : ;5752
123456 400 NULL
123456 155 NULL







Re: TSQL - Avoid duplicated rows - using distinct / Group by

Matt Tolhurst

Hi,

I am guessing that you have duplicates in one of those tables based on the joins, which is why you are getting two rows with the same remarks. Instead of the group by clause you could try selecting only the distinct values from the table with the duplicates in.

e.g.

with group by

select Title,YearlyIncome,City

from dbo.DimCustomer c inner join dbo.DimGeography g

on c.GeographyKey = g.GeographyKey

group by Title,YearlyIncome,City

with distinct

select Title,YearlyIncome,City

from dbo.DimCustomer c inner join (select distinct GeographyKey, City from dbo.DimGeography) as g

on c.GeographyKey = g.GeographyKey

It might be just as quick but worth a quick test, just look at the execution plan of both of them together.

But the way you did it is probably the way I would have done it, really depends on whether those duplicates really should exist i suppose. Might need data cleaning.

Hope that helps

Matt






Re: TSQL - Avoid duplicated rows - using distinct / Group by

ajliaks

Thanks Matt your suggestion was very usefull!

I want to look at the execution plan, but I don't know how to, could you...

Thanks,

Aldo.

Below the code I am using now:

Code Snippet

SELECT

Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',

Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',

Accounts.FILTER AS 'ACCOUNTS.FILTER',

Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',

JurnalTransMoves.SUF AS 'JURNALTRANSMOVES.SUF',

Stock.REMARKS AS 'STOCK.REMARKS',

JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID',

JurnalTrans.REF2 AS 'JURNALTRANS.REF2'

FROM

JURNALTRANSMOVES AS JurnalTransMoves

INNER JOIN (SELECT DISTINCT JURNALTRANSID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID

-- Every Match (Kabala) for the same Invoice generates a new line in RECEIPTJURNALMATCH.JURNALTRANSID

--Use Distinct to retrieve only one row for each Invoice

INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY

INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID

INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID

WHERE

JurnalTransMoves.ACCOUNTKEY IN ('123456')






Re: TSQL - Avoid duplicated rows - using distinct / Group by

Matt Tolhurst

Hi,

I can't run it without creating the tables and data. The link below tells you how to do it

http://msdn2.microsoft.com/en-us/library/ms189562.aspx

Hope that helps

Matt





Re: TSQL - Avoid duplicated rows - using distinct / Group by

ajliaks

Thanks again!