Adding ROW_NUMBER() to query loses DISTINCT, defeats the purpose of paged results attempt

Question

I'm having a problem bringing back a truly DISTINCT set of records using ROW_NUMBER() with a LEFT JOIN whose joined results are in turn concatenated into a comma-delimited list with the FOR XML PATH trick.

The objective is to implement paging in a web application because of the large number of records that can be returned and only bringing back a subset each time depending on what page number was selected.

Consider the following SQL:

SELECT DISTINCT IndustryCode, 
STUFF ( 
    (SELECT ',' + FE_LocationCode
    FROM tblLegacy_Codes i2
    WHERE c.IndustryCode = i2.IndustryCode
    FOR XML PATH(''))
,1,1,'') AS LegacyList
FROM Common_Clli c
LEFT JOIN tblLegacy_Codes legacy ON c.IndustryCode = legacy.IndustryCode
WHERE FE_LocationCode LIKE 'AUS%'

It brings back a very nice DISTINCT list as expected:

IndustryCode    LegacyList
AUSTTX85        AUST.GET,AUST.LDD,AUST.UU4
AUSTTXTE        AUST.TE,AUST.TEH
AUSUTX78        AUST.AX3
SCHWAS01        AUSC01C1,AUSC01UT
SZBGASAH        AUSB01C1,AUSB01OB,AUSB01TA
SZBGASAI        AUSB01SN,AUVI01SN,AUVI02SN
SZBGASAK        AUSB03C1,AUSB03V1

BUT - when the ROW_NUMBER syntax is added to the first line of the query above so that it looks like this:

SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY IndustryCode) AS rowNum, IndustryCode,...

The results now include a row for each IndustryCode for as many are in the LegacyList because DISTINCT of course now includes rowNum. Without including the entire result set, here is what is seen just for the first IndustryCode in the list because of its 3 associated child records:

rowNum  IndustryCode    LegacyList
1       AUSTTX85        AUST.GET,AUST.LDD,AUST.UU4
2       AUSTTX85        AUST.GET,AUST.LDD,AUST.UU4
3       AUSTTX85        AUST.GET,AUST.LDD,AUST.UU4

And of course the final implementation of paging is to be able to wrap all the SQL mentioned with

SELECT * FROM (
  [ the SQL you've already seen ]
WHERE rowNum BETWEEN x and y

For the moment I've had to resort to executing the SQL first without ROW_NUMBER() and putting the results into a temp table and then getting the final product from there. And unfortunately that has totally defeated the intention of minimizing load by only getting a subset of records every time.

I'm fairly convinced there's just something I don't know. It's always a safe bet. Many regards for help.

The final product that satisfies all the requirements ends up being the SQL below. While both the first 2 solutions offered were helpful, neither addressed the need to NOT house the entire results of the query first in a temporary style table, whether that table be a common table expression or something else. This StackOverflow thread led me to a complete resolution.

SELECT RowNum, IndustryCode, FEList FROM
(
    SELECT ROW_NUMBER() OVER(ORDER BY IndustryCode) AS RowNum, IndustryCode, FEList
    FROM
    (
        SELECT DISTINCT IndustryCode,
        STUFF (
            (SELECT ',' + FE_LocationCode
            FROM tblLegacyCodes i2
            WHERE c.IndustryCode = i2.IndustryCode
            FOR XML PATH(''))
        ,1,1,'') AS LegacyList
        FROM Common_Clli c
        LEFT JOIN tblLegacyCodes legacy ON c.IndustryCode = legacy.IndustryCode
        WHERE FE_LocationCode LIKE 'AUS%'
    ) subInner
) subOuter
WHERE RowNum BETWEEN x AND y

Answer 1

What about

SELECT ROW_NUMBER() OVER(ORDER BY IndustryCode) AS rowNum, IndustryCode, LegacyList
FROM
(
SELECT DISTINCT IndustryCode, 
STUFF ( 
    (SELECT ',' + FE_LocationCode
    FROM tblLegacy_Codes i2
    WHERE c.IndustryCode = i2.IndustryCode
    FOR XML PATH(''))
,1,1,'') AS LegacyList
FROM Common_Clli c
LEFT JOIN tblLegacy_Codes legacy ON c.IndustryCode = legacy.IndustryCode
WHERE FE_LocationCode LIKE 'AUS%'
) T
ORDER BY rowNum

Answer 2

You should use table expressions, like

WITH MYTABLE AS (
SELECT DISTINCT IndustryCode, 
STUFF ( 
    (SELECT ',' + FE_LocationCode
    FROM tblLegacy_Codes i2
    WHERE c.IndustryCode = i2.IndustryCode
    FOR XML PATH(''))
,1,1,'') AS LegacyList
FROM Common_Clli c
LEFT JOIN tblLegacy_Codes legacy ON c.IndustryCode = legacy.IndustryCode
WHERE FE_LocationCode LIKE 'AUS%'
) SELECT ROW_NUMBER() OVER(ORDER BY IndustryCode) AS ROWNUM
, IndustryCode, LegacyList FROM MYTABLE

Answer 3

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

has too many arguments specified.

Error solved in Arguments

@productCodeId varchar(15),
@productName varchar(60), 
@categoryCodeId varchar(15),
@subCategoryCodeId varchar(15),
@childSubCategoryCodeId varchar(15),
@productKeyword varchar(max),
@productCode varchar(25),
@productImage image=null,
@productDescShort varchar(max),
@ProductDescFull varchar(max),
@qualityProcessCertification varchar(max),
@userCodeId varchar(15),

 @productInfoCodeId varchar(15),
@productAdvantage varchar(max),
@productPriceInformation varchar(25),
@productAddPrice decimal(10,0),
@sampleAvailability varchar(5),
@deliveryLeadTime varchar(50),
@deliveryTerm varchar(max),
@minQuantity varchar(25),
@status varchar(15),


@id varchar(15)=null output ,
@id1 varchar(15)=null output   

AS BEGIN

--declare @id varchar(15)=null --declare @id1 varchar(15)=null

select @id=tp.productCodeId, @id1=tpi.productCodeId from tbl_product as tp inner join tbl_ProductInformation as tpi on tp.productCodeId= tpi.productCodeId where tp.productCodeId=@productCodeId and tpi.productCodeId=@productCodeId

if @id=@productCodeId and @id1=@productCodeId

begin update tbl_product set
productName=@productName,categoryCodeId=@categoryCodeId, subCategoryCodeId=@subCategoryCodeId,childSubCategoryCodeId=@childSubCategoryCodeId, productKeyword=@productKeyword,productCode=@productCode, productImage=@productImage, productDescShort=@productDescShort, ProductDescFull=@ProductDescFull,qualityProcessCertification=@qualityProcessCertification, userCodeId=@userCodeId FROM tbl_product tp, tbl_ProductInformation tpi where tp.productCodeId= @productCodeId and tpi.productCodeId=@productCodeId

update tbl_ProductInformation set productInfoCodeId=@productInfoCodeId, productAdvantage=@productAdvantage,
productPriceInformation=@productPriceInformation,
productAddPrice=@productAddPrice,sampleAvailability=@sampleAvailability,deliveryLeadTime=@deliveryLeadTime,
deliveryTerm=@deliveryTerm,minQuantity=@minQuantity,productCodeId=@productCodeId
FROM tbl_product tp, tbl_ProductInformation tpi

where tp.productCodeId=@productCodeId and tpi.productCodeId=@productCodeId end
else begin
insert into tbl_product(productCodeId,productName,categoryCodeId, subCategoryCodeId,childSubCategoryCodeId,productKeyword,productCode,productImage,productDescShort, ProductDescFull,qualityProcessCertification,userCodeId) values(@productCodeId,@productName,@categoryCodeId, @subCategoryCodeId,@childSubCategoryCodeId,@productKeyword,@productCode,@productImage,@productDescShort, @ProductDescFull,@qualityProcessCertification,@userCodeId)

insert into tbl_ProductInformation(productInfoCodeId,productAdvantage,
productPriceInformation,productAddPrice,sampleAvailability,deliveryLeadTime,deliveryTerm,minQuantity,productCodeId,status)
values(@productInfoCodeId,@productAdvantage,@productPriceInformation,@productAddPrice,
@sampleAvailability,@deliveryLeadTime,@deliveryTerm,@minQuantity,@productCodeId,@status)

end