CODEWITHSUNDEEP
t-sql
user2689499
user2689499

Reputation: 1

DISTINCT isn't removing dupes

I am not sure how to use DISTINCT in an AB BA fashion. For instance, I have two columns BoughtLoyaltyProgramId, SoldLoyaltyProgramId. But even when I use DISTINCT, it produces a duplicate when the same code in a boughtloyaltyprogramid appears in soldloyaltyprogramid. I want no dupes but I have no idea how this works with multiple columns and pairings.

Here is the stored procedure:

ALTER PROC AA
    @LPPProgramID UNIQUEIDENTIFIER ,
    @DateFrom DATETIME ,
    @DateTo DATETIME
AS 
SELECT DISTINCT TOP ( 5 )
        BoughtLoyaltyProgramId ,
        SoldLoyaltyProgramId ,
        DateTransactionCleared ,
        ExchangeRate
FROM    dbo.PEX_ClearedTransactions
WHERE   DateTransactionCleared >= @DateFrom
        AND DateTransactionCleared < @DateTo
        AND ( BoughtLoyaltyProgramId = @LPPProgramID
              OR SoldLoyaltyProgramId = @LPPProgramID
            )
ORDER BY ExchangeRate;

GO

Upvotes: 0

Views: 79

Answers (3)

Dennis Jaheruddin
Dennis Jaheruddin

Reputation: 21563

Here is how you can get all distinct values from two columns:

SELECT distinct * from
(SELECT BoughtLoyaltyProgramId
FROM dbo.PEX_ClearedTransactions
UNION ALL 
SELECT SoldLoyaltyProgramId
FROM dbo.PEX_ClearedTransactions) as A

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460208

Perhaps you want to use ROW_NUMBER:

WITH cte 
     AS (SELECT boughtloyaltyprogramid, 
                soldloyaltyprogramid, 
                datetransactioncleared, 
                exchangerate, 
                RN=Row_number() OVER( 
                    partition BY boughtloyaltyprogramid, soldloyaltyprogramid 
                    ORDER BY exchangerate) 
         FROM   dbo.pex_clearedtransactions 
         WHERE  datetransactioncleared >= @DateFrom 
                AND datetransactioncleared < @DateTo 
                AND ( boughtloyaltyprogramid = @LPPProgramID 
                       OR soldloyaltyprogramid = @LPPProgramID )) 
SELECT TOP(5) * FROM   cte 
WHERE  RN = 1 
ORDER  BY exchangerate

Upvotes: 0

Chris Hammond
Chris Hammond

Reputation: 8943

Distinct is per ROW, so the value in the columns in a row are in a distinct combination, the data isn't compared in each column of a row, to other columns in that row.

You likely will also want to do some comparison in your Where statement for the column data.

Upvotes: 1

Related Questions