Solved

Adding row number to a query and use it in where clause

Posted on 2015-02-11
3
102 Views
Last Modified: 2015-02-11
Hi,

I have a query looking like this:
SELECT DISTINCT customerID, firstname, lastname, firstname + ' ' + lastname AS Name, ISNULL(familyName, ' ') AS familyName, CountryID, address, co, zip, city, Country, countryCode, countryCode + '-' + zip + ' ' + city AS postal, phone, mobile, email, ocr, printText, aviText, logo, InvoiceAddress, amount, defaultAccount, juridisktnamn, kontrollsiffra 
from ( 
select DISTINCT c.customerID, c.firstname, c.lastname, f.familyName, cfm.familyID , IsDeleted, cpo.customerPrintActivityID,c.CountryID, address, co, zip, city, Country, countryCode, phone, mobile, email, ocr, cpa.printText, aviText, logo, InvoiceAddress, amount, o.defaultAccount, o.juridisktnamn, cpo.kontrollsiffra, row_number() over (partition by  cfm.familyID order by c.customerid ) rn, ROW_NUMBER() OVER (ORDER BY c.CustomerID, lastname) AS row 
from customer c 
left join customerFamilyMember cfm on cfm.customerid = c.customerid 
left join customerFamily f on f.familyID = cfm.familyID 
INNER JOIN dbo.customerPrintOut AS cpo ON c.CustomerID = cpo.customerID  
INNER JOIN dbo.customerPrintActivity AS cpa ON cpo.customerPrintActivityID = cpa.customerPrintActivityID 
INNER JOIN dbo.Organisation AS o ON cpa.orgID = o.OrgID 
WHERE cpa.customerPrintActivityID = @customerPrintActivityID 
) sq 
WHERE sq.rn = 1 Or sq.familyID Is NULL And IsDeleted = 0 And sq.customerPrintActivityID = @customerPrintActivityID AND sq.row BETWEEN 1 AND 10
ORDER BY customerID, lastname

Open in new window


In this query I of course hope to get the first 10 rows of the result, but the result includes many more rows.

Then I tried to alter the query to look like this:
SELECT DISTINCT customerID, firstname, lastname, firstname + ' ' + lastname AS Name, ISNULL(familyName, ' ') AS familyName, CountryID, address, co, zip, city, Country, countryCode, countryCode + '-' + zip + ' ' + city AS postal, phone, mobile, email, ocr, printText, aviText, logo, InvoiceAddress, amount, defaultAccount, juridisktnamn, kontrollsiffra 
from ( 
select DISTINCT c.customerID, c.firstname, c.lastname, f.familyName, cfm.familyID , IsDeleted, cpo.customerPrintActivityID,c.CountryID, address, co, zip, city, Country, countryCode, phone, mobile, email, ocr, cpa.printText, aviText, logo, InvoiceAddress, amount, o.defaultAccount, o.juridisktnamn, cpo.kontrollsiffra, row_number() over (partition by  cfm.familyID order by c.customerid ) rn, ROW_NUMBER() OVER (ORDER BY c.CustomerID, lastname) AS row 
from customer c 
left join customerFamilyMember cfm on cfm.customerid = c.customerid 
left join customerFamily f on f.familyID = cfm.familyID 
INNER JOIN dbo.customerPrintOut AS cpo ON c.CustomerID = cpo.customerID  
INNER JOIN dbo.customerPrintActivity AS cpa ON cpo.customerPrintActivityID = cpa.customerPrintActivityID 
INNER JOIN dbo.Organisation AS o ON cpa.orgID = o.OrgID 
WHERE cpa.customerPrintActivityID = @customerPrintActivityID [b][i]AND row BETWEEN 1 AND 10[/i][/b]
) sq 
WHERE sq.rn = 1 Or sq.familyID Is NULL And IsDeleted = 0 And sq.customerPrintActivityID = @customerPrintActivityID AND sq.row BETWEEN 1 AND 10
ORDER BY customerID, lastname

Open in new window


But then I get this error:

Msg 207, Level 16, State 1, Line 13
Invalid column name 'row'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'row'.

If anyone got a clue how I can order my result and then limit with a WHERE clause I would be most grateful.

Peter
0
Comment
Question by:peternordberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40602941
You need to add the keyword TOP for that:
SELECT TOP 10 DISTINCT customerID, firstname, lastname, firstname + ' ' + lastname AS Name, ISNULL(familyName, ' ') AS familyName, CountryID, address, co, zip, city, Country, countryCode, countryCode + '-' + zip + ' ' + city AS postal, phone, mobile, email, ocr, printText, aviText, logo, InvoiceAddress, amount, defaultAccount, juridisktnamn, kontrollsiffra 
from ( 
select DISTINCT c.customerID, c.firstname, c.lastname, f.familyName, cfm.familyID , IsDeleted, cpo.customerPrintActivityID,c.CountryID, address, co, zip, city, Country, countryCode, phone, mobile, email, ocr, cpa.printText, aviText, logo, InvoiceAddress, amount, o.defaultAccount, o.juridisktnamn, cpo.kontrollsiffra, row_number() over (partition by  cfm.familyID order by c.customerid ) rn, ROW_NUMBER() OVER (ORDER BY c.CustomerID, lastname) AS row 
from customer c 
left join customerFamilyMember cfm on cfm.customerid = c.customerid 
left join customerFamily f on f.familyID = cfm.familyID 
INNER JOIN dbo.customerPrintOut AS cpo ON c.CustomerID = cpo.customerID  
INNER JOIN dbo.customerPrintActivity AS cpa ON cpo.customerPrintActivityID = cpa.customerPrintActivityID 
INNER JOIN dbo.Organisation AS o ON cpa.orgID = o.OrgID 
WHERE cpa.customerPrintActivityID = @customerPrintActivityID 
) sq 
WHERE sq.rn = 1 Or sq.familyID Is NULL And IsDeleted = 0 And sq.customerPrintActivityID = @customerPrintActivityID AND sq.row BETWEEN 1 AND 10
ORDER BY customerID, lastname

Open in new window

0
 
LVL 12

Accepted Solution

by:
Saurabh Bhadauria earned 500 total points
ID: 40602952
you can not use row column in inner query...  as it is out of scope...

WHERE cpa.customerPrintActivityID = @customerPrintActivityID
            AND row BETWEEN 1
                  AND 10


And you are getting more then 10 rows  from first query because of OR clause in where

WHERE sq.rn = 1
      OR sq.familyID IS NULL
      AND IsDeleted = 0
      AND sq.customerPrintActivityID = @customerPrintActivityID
      AND sq.row BETWEEN 1
            AND 10





Thanks,
Saurabh
0
 
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 40602956
It's the inner query ("sq") that's causing the problem. The ROW_NUMBER windowing function is applied as part of the SELECT which will not be available when the WHERE is evaluated.

One way would be to move the result set into a temporary table and then apply the filter, something on the following lines (I don't have SSMS right now, so there may be some issues with this query):

IF OBJECT_ID('tempdb..#temporaryResults', 'U') IS NOT NULL 
    DROP TABLE #temporaryResults ;

SELECT DISTINCT
        c.customerID,
        c.firstname,
        c.lastname,
        f.familyName,
        cfm.familyID,
        IsDeleted,
        cpo.customerPrintActivityID,
        c.CountryID,
        address,
        co,
        zip,
        city,
        Country,
        countryCode,
        phone,
        mobile,
        email,
        ocr,
        cpa.printText,
        aviText,
        logo,
        InvoiceAddress,
        amount,
        o.defaultAccount,
        o.juridisktnamn,
        cpo.kontrollsiffra,
        row_number() OVER ( PARTITION BY cfm.familyID ORDER BY c.customerid ) rn,
        ROW_NUMBER() OVER ( ORDER BY c.CustomerID, lastname ) AS row
INTO    #temporaryResults
FROM    customer c
        LEFT JOIN customerFamilyMember cfm ON cfm.customerid = c.customerid
        LEFT JOIN customerFamily f ON f.familyID = cfm.familyID
        INNER JOIN dbo.customerPrintOut AS cpo ON c.CustomerID = cpo.customerID
        INNER JOIN dbo.customerPrintActivity AS cpa ON cpo.customerPrintActivityID = cpa.customerPrintActivityID
        INNER JOIN dbo.Organisation AS o ON cpa.orgID = o.OrgID
WHERE   cpa.customerPrintActivityID = @customerPrintActivityID 


SELECT DISTINCT
        customerID,
        firstname,
        lastname,
        firstname + ' ' + lastname AS Name,
        ISNULL(familyName, ' ') AS familyName,
        CountryID,
        address,
        co,
        zip,
        city,
        Country,
        countryCode,
        countryCode + '-' + zip + ' ' + city AS postal,
        phone,
        mobile,
        email,
        ocr,
        printText,
        aviText,
        logo,
        InvoiceAddress,
        amount,
        defaultAccount,
        juridisktnamn,
        kontrollsiffra
FROM    ( SELECT    *
          FROM      #temporaryResults AS tr
          WHERE     tr.row BETWEEN 1 AND 10
        ) sq
WHERE   sq.rn = 1
        OR sq.familyID IS NULL
        AND IsDeleted = 0
        AND sq.customerPrintActivityID = @customerPrintActivityID
        AND sq.row BETWEEN 1 AND 10
ORDER BY customerID,
        lastname

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question