How to get distinct records in sqlserver

84 views Asked by At
FUNDFAMILY_ID    FUND_NAME                REPORTSECTION_ID           
--------------------------------------------------------------
172295295    VANGUARD GROWTH FUND (Retail)  126345
172295295    VANGUARD GROWTH FUND (Retail)  126354
--------------------------------------------------------------

But i need to get the results like below:

FUNDFAMILY_ID     FUND_NAME                   REPORTSECTION_ID
--------------------------------------------------------------
172295295   VANGUARD GROWTH FUND (Retail)   126345

Inside a stored procedure i have a query like below which gives duplicate rows

    INSERT INTO #TABLE_REPORTSECTIONDATA (FUND_NAME, FUNDFAMILY_ID, REPORTSECTION_ID)

                SELECT DISTINCT @FUNDNAME_TEMP , @FUNDID_TEMP, EntryPointViewsId
                FROM EntryPointViews EPV
                ORDER BY EntryPointViewsId

DISTINCT keyword is not giving me the correct results.please suggest

2

There are 2 answers

0
Tim Schmelter On BEST ANSWER

DISTINCT is giving you the correct result, the records are distinct by all columns you have specified. What do you want instead? Just unique according to the ID and the Description? Which AnotherID want to take from each group?

If you just want an arbitrary you can use MIN or MAX with GROUP BY:

SELECT ID, Description, MIN(AnotherID)
FROM EntryPointViews EPV
GROUP BY ID, Description
0
Evaldas Buinauskas On

I think you're looking for ROW_NUMBER() with partitioning, this query should work for you:

SELECT FUNDFAMILY_ID, FUND_NAME, REPORTSECTION_ID
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY FUNDFAMILY_ID ORDER BY REPORTSECTION_ID) AS RN, FUNDFAMILY_ID, FUND_NAME, REPORTSECTION_ID, EntryPointViewsId
    FROM EntryPointViews EPV
    ) AS T
WHERE RN = 1
ORDER BY EntryPointViewsId;