use Row_number after applying distinct

Question

I am creating an SP which gives some result by applying distinct on it, now I want to implement sever side paging, so I tried using Row_number on distinct result like:

    WITH CTE AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY tblA.TeamName DESC)
as Row,tblA.TeamId,tblA.TeamName,tblA.CompId,tblA.CompName,tblA.Title,tblA.Thumbnail,tblA.Rank,tblA.CountryId,tblA.CountryName
         FROM
         (
           --The table query starts with SELECT 
         )tblA
    )
        SELECT CTE.* FROM CTE
        WHERE CTE.Row BETWEEN @StartRowIndex AND @StartRowIndex+@NumRows-1
        ORDER BY CTE.CountryName

but rows are first assigned RowNumber then distinct get applied that is why I am getting duplicate values, how to get distinct rows first then get row numbers for the same.

Any solution on this? Am I missing something? need answer ASAP. thanks in advance!

Answer 1

Don't you need to add "partition by" to your ROW_NUMBER statement?

ROW_NUMBER() OVER(Partition by ___, ___, ORDER BY tblA.TeamName DESC) 

In the blank spaces, place the column names you would like to create a new row number for. Duplicates will receive a number that is NOT 1 so you might not need the distinct.

To gather the unique values you could write a subquery where the stored procedure only grabs the rows with a 1 in them.

select * from
(
  your code
) where row = 1

Hope that helps.

I'm not sure why you're doing this:

WHERE CTE.Row BETWEEN @StartRowIndex AND @StartRowIndex+@NumRows-1