sql - Removing duplicate records before numbering rows in result -

sql - Removing duplicate records before numbering rows in result -

so, know plenty sql unsafe , next illustration pull 1 page's worth of records table:

select top #arguments.perpage# * ( select distinct row_number() over(order (select 1)) rownum, productdiagramparts.productdiagramid productdiagramid, products.id productid, products.title producttitle, totalrows = count(*) over() manufacturers inner bring together products on manufacturers.id = products.manufacturerid inner bring together productdiagramparts on products.id = productdiagramparts.productid inner bring together productdiagrams on productdiagramparts.productdiagramid = productdiagrams.id #whereclause# ) _tmpinlineview rownum > #offset# order producttitle

the select top wrapped around of course of study pulls records current page. problem is, there duplicates in innermost select statement want remove, using distinct doesn't work shown above because rows have been numbered outer query. how can create innermost select results distinct before numbering rows?

here's solution based on accepted reply below:

select top #arguments.perpage# * (

select row_number() over(order (select 1)) rownum, productdiagramid, productid, producttitle, totalrows = count(*) over() (

select distinct productdiagramparts.productdiagramid productdiagramid, products.id productid, products.title producttitle manufacturers inner bring together products on manufacturers.id = products.manufacturerid inner bring together productdiagramparts on products.id = productdiagramparts.productid inner bring together productdiagrams on productdiagramparts.productdiagramid = productdiagrams.id #whereclause#

) _tmpdupremove

) _tmpinlineview

where rownum > #offset# order producttitle

here's approach uses row_number function sec time. inner-most select assigns row-numbers based on groups of duplicates. then, rows row-number of 1 returned remove duplicates. and, finally, process done assign row number.

select top #arguments.perpage# * ( select row_number() over(order (select 1)) rownum, productdiagramparts.productdiagramid productdiagramid, products.id productid, products.title producttitle, totalrows = count(*) over() ( select row_number() on partition productdiagramparts.productdiagramid, products.id order (select 1)) dup_sequence, * manufacturers inner bring together products on manufacturers.id = products.manufacturerid inner bring together productdiagramparts on products.id = productdiagramparts.productid inner bring together productdiagrams on productdiagramparts.productdiagramid = productdiagrams.id #whereclause# ) _tmpdupremove dup_sequence = 1 ) _tmpinlineview rownum > #offset# order producttitle

sql sql-server tsql