SQL Total Row Count (DENSE_RANK/DISTINCT)

I am selecting records that may contain duplicates, I need to be able to paginate a distinct result set.

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 labels them 1, 2, and 3

My problem is paginationTotalRows still returns 10 (the original duplicates-included) result-set count, how can I modify the query so that paginationTotalRows returns the correct amount?

Solution

Move the windowing function outside the select distinct. I would 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 partition and, if not partitioned it cannot repeat a number at all. DENSE_RANK() however can repeat a number within any partition and, if not partitioned can still repeat numbers. For pagination to be utterly predictable you need utterly predictable row numbering, so use ROW_NUMBER(). [please]