Clever Geek Handbook

Removing duplicate records before line numbering in the result

So, I know SQL is dangerous enough and I follow the example to pull records from one table from a table:

SELECT TOP #arguments.perPage# * FROM (

SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownum, productdiagramparts.productdiagramid AS productdiagramid, products.id AS productid, products.title AS producttitle, totalRows = COUNT(*) OVER()
FROM manufacturers
INNER JOIN products ON manufacturers.id = products.manufacturerid
INNER JOIN productdiagramparts ON products.id = productdiagramparts.productid
INNER JOIN productdiagrams ON productdiagramparts.productdiagramid = productdiagrams.id
WHERE #whereClause#
) _tmpInlineView

WHERE rownum > #offset#
ORDER BY producttitle

The SELECT TOP wrapped around this of course only pulls in the records for the current page. The problem is that there are duplicates in the innermost SELECT statement I want to remove, but using DISTINCT doesn't work as shown above because the rows are already numbered for the outer query. How can I make my innermost SELECT results clear before row numbering?

Here's a solution based on the accepted answer below:

SELECT TOP # arguments.perPage # * FROM (

SELECT ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS rownum, productdiagramid, productid, producttitle, totalRows = COUNT (*) OVER () FROM (

SELECT DISTINCT productdiagramparts.productdiagramid AS productdiagramid, products.id AS productid, products.title AS producttitle FROM manufacturers INNER JOIN products ON manufacturer.id = products.manufacturerid INNER JOIN productdiagramparts ONagraidid = productdiagramparts.productid product INNER JOIN products = productdiagrams.id WHERE #whereClause #

) _tmpDupRemove

) _tmpInlineView

WHERE rownum> # offset # ORDER BY producttitle

+3


source to share


3 answers


Here's an approach that uses the function a ROW_NUMBER

second time. The innermost one SELECT

assigns line numbers based on groups of duplicates. Then only rows with line number 1 are returned to remove duplicates. Finally, the process is performed to assign a row number.



SELECT TOP #arguments.perPage# *
FROM (

    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownum,
        productdiagramparts.productdiagramid AS productdiagramid, 
        products.id AS productid, products.title AS producttitle, totalRows = COUNT(*) OVER()
    FROM (

        SELECT ROW_NUMBER() OVER PARTITION BY productdiagramparts.productdiagramid, products.id ORDER BY (SELECT 1)) AS dup_sequence, *
        FROM manufacturers
        INNER JOIN products ON manufacturers.id = products.manufacturerid
        INNER JOIN productdiagramparts ON products.id = productdiagramparts.productid
        INNER JOIN productdiagrams ON productdiagramparts.productdiagramid = productdiagrams.id
        WHERE #whereClause#

        ) _tmpDupRemove
    WHERE dup_sequence = 1

    ) _tmpInlineView

WHERE rownum > #offset#
ORDER BY producttitle
+1


source


You can use the GROUP BY clause .

Order in SQLServer: GROUP BY -> SELECT ROW_NUMBER () -> DISTINCT. Therefore, you need GROUP BY instead of DISTINCT.



SELECT TOP #arguments.perPage# * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownum, 
       productdiagramparts.productdiagramid AS productdiagramid, 
       products.id AS productid, 
       products.title AS producttitle, 
       totalRows = COUNT(*) OVER()
FROM manufacturers
INNER JOIN products ON manufacturers.id = products.manufacturerid
INNER JOIN productdiagramparts ON products.id = productdiagramparts.productid
INNER JOIN productdiagrams ON productdiagramparts.productdiagramid = productdiagrams.id
GROUP BY productdiagramparts.productdiagramid, products.id, products.title
WHERE #whereClause#
) _tmpInlineView
WHERE rownum > #offset#
ORDER BY producttitle
0


source 


DECLARE @startrow Int = 0
DECLARE @recsperpage Int = 10


SELECT  [Inner2].[productdiagramid], 
        [Inner2].[productid], 
        [Inner2].[producttitle]
FROM        
    (
    SELECT ROW_NUMBER() OVER 
        (ORDER BY  
              [Inner1].[producttitle]) AS [ROW_NUMBER], 
        [Inner1].[productdiagramid], 
        [Inner1].[productid], 
        [Inner1].[producttitle]
    FROM
        (
        SELECT DISTINCT pdp.productdiagramid, p.id as productid, p.title as producttitle
        FROM manufacturers m
        INNER JOIN products p ON m.id = p.manufacturerid
        INNER JOIN productdiagramparts pdp ON p.id = pdp.productid
        INNER JOIN productdiagrams pd ON pdp.productdiagramid = pd.id
        WHERE #whereclause#
        ) AS Inner1
    ) as Inner2
WHERE Inner2.[ROW_NUMBER] BETWEEN @startrow + 1 AND  @startrow + @recsperpage
ORDER BY [Inner2].[ROW_NUMBER]
0


source






More articles:


All Articles