SQL Total Row Count (DENSE_RANK / DISTINCT)

Question

I select records that may contain duplicates, I need to be able to split the result set into separate elements.

At the moment, I have the following (simplified) query:

SELECT pagiWrapper.* FROM ( SELECT DISTINCT alias.id , alias.name , DENSE_RANK() OVER (ORDER BY alias.name ASC) AS 'paginationRowNo' , COUNT(*) OVER(PARTITION BY 1) AS 'paginationTotalRows' FROM MyTable alias LEFT JOIN MyTableTwo alias2 ON alias2.id = alias.id WHERE ( /* condition */) ) pagiWrapper WHERE pagiWrapper.paginationRowNo > 0 AND pagiWrapper.paginationRowNo <= 15 

There are 10 records in this result set, however DISTINCT correctly returns 3, and DENSE_RANK correctly marks them 1 , 2 and 3

My paginationTotalRows problem still returns 10 (the original duplicate) in the number of results, how can I change the query so that paginationTotalRows returns the correct amount?

Answer 1

Move the windowing function outside of select distinct . I also suggest you use row_number () instead of dense_rank ().

 SELECT pagiWrapper.* FROM ( SELECT iq.* , ROW_NUMBER() OVER (ORDER BY iq.name ASC) AS 'paginationRowNo' , COUNT(*) OVER (PARTITION BY 1) AS 'paginationTotalRows' FROM ( SELECT DISTINCT alias.id , alias.name , alias2.something_I_hope FROM MyTable alias LEFT JOIN MyTableTwo alias2 ON alias2.id = alias.id WHERE (1 = 1 /* condition */) ) iq ) pagiWrapper WHERE pagiWrapper.paginationRowNo > 0 AND pagiWrapper.paginationRowNo <= 15 

I recommend ROW_NUMBER() for pagination. This function cannot repeat a number within a section, and if it is not divided, it cannot repeat a number at all. DENSE_RANK() however can repeat a number inside any section and, if not partitioned, it can still repeat numbers. For pagination to be completely predictable, you need absolutely predictable line numbering, so use ROW_NUMBER() . [You are welcome]

Answer 2

Try to find MAX(paginationRowNo) in the top query:

  SELECT pagiWrapper.* FROM( SELECT *, MAX(paginationRowNo) OVER(PARTITION BY 1) as 'paginationTotalRows' FROM ( SELECT DISTINCT alias.id , alias.name , DENSE_RANK() OVER (ORDER BY alias.name ASC) AS 'paginationRowNo' FROM MyTable alias LEFT JOIN MyTableTwo alias2 ON alias2.id = alias.id WHERE ( /* condition */) ) as PW ) pagiWrapper WHERE pagiWrapper.paginationRowNo > 0 AND pagiWrapper.paginationRowNo <= 15