[SOLVED] How to get results with distinct values when we use Group by

How to get results with distinct values when we use Group by

How we should reslove this query, i didn't find solution for drop the second row for each Group . Is there a function or something which can return only distinct values for groups with rolllup (here distinct can't be used )

SELECT 
    -- ROW_NUMBER() over (ORDER BY FF_DATE asc) as "numSituation"
     FF_NUMERO as "numFacture"
    , FF_DATE as "dateFacture"
    , FF_DATEREG as "dateReglement"
    , SUM(FF_D_HT) as "montantHT"
    , SUM(FF_D_TTC) as "montantTTC"
FROM F_FACTURESFOU
 INNER JOIN F_LIGNEFACFOU ON LFF_FFPTR = FF_NUMERO
 INNER JOIN F_LOT ON LFF_LT_IDENT = LT_IDENT
WHERE LFF_CODE_AFF = '14031-00' AND LT_CODE IN ('S83') AND FF_DATEVALID is not null
GROUP BY FF_NUMERO , FF_DATE, FF_DATEREG WITH ROLLUP 
HAVING ( ( FF_NUMERO is not null and FF_DATE IS NOT NULL ) OR ( FF_NUMERO is null and FF_DATE is null ))
ORDER BY GROUPING(FF_NUMERO ), FF_NUMERO, GROUPING ( FF_DATE), FF_DATE , GROUPING ( FF_DATEREG), FF_DATEREG

This is the result:

15E1500062  2015-07-24 00:00:00.000 NULL    3000.00 3600.00
15E1500062  2015-07-24 00:00:00.000 NULL    3000.00 3600.00  
15E1500063  2015-07-31 00:00:00.000 NULL    6000.00 7200.00
15E1500063  2015-07-31 00:00:00.000 NULL    6000.00 7200.00
15E1600423  2015-09-11 00:00:00.000 NULL    3000.00 3600.00
15E1600423  2015-09-11 00:00:00.000 NULL    3000.00 3600.00
NULL                      NULL      NULL    12000.00 14400.00

Solution

You can use SQL Row_Number() function with Partition By clause You can number rows in the result set within each group defined by Partition By clause

Then you can exclude all rows with row_number value greater or equal to 2

Please check SQL tutorial Row_Number() Over Partition By to delete duplicate rows in SQL Server

ROW_NUMBER() over (Partition By FF_NUMERO, FF_DATE ORDER BY

You can use SQL CTE expression as follows

;with cte as (
SELECT 
     ROW_NUMBER() over (Partition By FF_NUMERO, FF_DATE ORDER BY .... DESC) as rn,
    ....
FROM ....
)
SELECT * from cte where rn = 1