SQL Server DISTINCT pagination using ROW_NUMBER () is no different

Question

Ok, so basically my DISTINCT useless with ROW_NUMBER() , I need to avoid this as it causes duplicate results (except for a unique amount of course!)

So, I am looking for a query that will work the same, but does not have duplicate rows, because num is unique:

 WITH t AS ( SELECT DISTINCT *, ROW_NUMBER() OVER (ORDER BY Date) AS num FROM Original_Import LEFT JOIN eqcas.dbo.BASE_PROXY_VIEW_WITHTARGET ON ADName = Targetuser WHERE (BaseProxy = 'agmc' OR ADName = 'agmc') AND (Commited IS NULL OR Commited = 0) ) SELECT DISTINCT ID, num, ADName, Description_User, Description_Amex, Amount, Date FROM t WHERE (t.BaseProxy = 'agmc' OR t.ADName = 'agmc') AND num BETWEEN 0 AND 20 AND (Commited IS NULL OR Commited = 0) ORDER BY Date 

Probably pretty trivial to fix, but, seeing that I'm not a SQL Server guy, I'm not used to these internal queries, etc.

Update: yes, num is used for swap.

Answer 1

Limited confidence in this, as I cannot verify or even compile, but this is the general essence of what I thought ...

 WITH t AS ( SELECT [insert your fields here], ROW_NUMBER() OVER (ORDER BY Date) AS num FROM ( SELECT DISTINCT *[insert your fields here] FROM Original_Import LEFT JOIN eqcas.dbo.BASE_PROXY_VIEW_WITHTARGET ON ADName = Targetuser WHERE (BaseProxy = 'agmc' OR ADName = 'agmc') AND (Commited IS NULL OR Commited = 0)) as X ) SELECT DISTINCT ID, num, ADName, Description_User, Description_Amex, Amount, Date FROM t WHERE (t.BaseProxy = 'agmc' OR t.ADName = 'agmc') AND num BETWEEN 0 AND 20 AND (Commited IS NULL OR Commited = 0) ORDER BY Date 

Answer 2

I seem to be two years late from my recent blog post about ROW_NUMBER() SELECT that DENSE_RANK() matches SELECT DISTINCT . Your CTE should be replaced by the following:

 WITH t AS ( SELECT DISTINCT *, DENSE_RANK() OVER (ORDER BY Date, ...) AS num FROM Original_Import LEFT JOIN eqcas.dbo.BASE_PROXY_VIEW_WITHTARGET ON ADName = Targetuser WHERE (BaseProxy = 'agmc' OR ADName = 'agmc') AND (Commited IS NULL OR Commited = 0) ) SELECT ... 

In the above query, the DENSE_RANK() ORDER BY would have to display all columns from Original_Import and from BASE_PROXY_VIEW_WITH_TARGET in order to reproduce the same order as the DISTINCT keyword. This will assign exactly one rank for one duplicate record set, so that DISTINCT will work again.

In the link to the blog post , I also included a link to SQLFiddle , illustrating this in a more trivial example.

 SELECT DISTINCT v, DENSE_RANK() OVER (w) row_number FROM t WINDOW w AS (ORDER BY v) ORDER BY v, row_number 

An explanation of why DISTINCT deletes duplicate rows after computed window functions can be found here .