Distinct on paged result , SQL Server query

Question

Hi

How can I distinct result of paged query? I want to distinct results that I paged using SQL Server 2005 paging method

SELECT * FROM
   (SELECT ROW_NUMBER() OVER (ORDER BY [dbo].[Tbl_Kartabl].[FKLoginID]) AS Row, [dbo].[Tbl_Kartabl].[FKLoginID] 
    FROM [dbo].[Tbl_Kartabl]) AS PagedResults 
WHERE Row >= 1 AND Row <= 10 

How can I do this?

How can I implement this in SubSonic 2?

Answer 1

In your innermost query (the from clause) you'll need to either use select distinct or use group by to pull out only the unique elements before ordering them.

SELECT [Row], [FKLoginID]
FROM (SELECT ROW_NUMBER() OVER ( ORDER BY T.[FKLoginID]) AS Row, T.[FKLoginID]
    FROM (SELECT DISTINCT [FKLoginID] FROM [dbo].[Tbl_Kartabl]) AS T ) AS PagedResults
    WHERE Row >= 1 AND Row <= 10