CODEWITHSUNDEEP
sqlsql-server
James Andrew Smith
James Andrew Smith

Reputation: 1566

Querying data from ROW_NUMBER result

Hi I am trying to query data based on the ROW_NUMBER() function.

SELECT ROW_NUMBER() OVER(ORDER BY Id) AS CountRow,
ProductId, ProductName, Tracklink, ProductImage, TrackPrice FROM ProductDetails;

I have my row with the numbers in CountRow column. Now I want to query this Alias to find rows for example between 31 and 40. But I get an error saying CountRow does not exist.

How can I overcome this issue?

thanks....

Upvotes: 3

Views: 134

Answers (3)

Kaf
Kaf

Reputation: 33809

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Id) AS CountRow,
ProductId, ProductName, Tracklink, ProductImage, TrackPrice FROM ProductDetails) AS A
WHERE CountRow BETWEEN 31 AND 40

Hope this helps.

Upvotes: 2

Arion
Arion

Reputation: 31239

If your DBMS support cte you can do it like this:

;WITH CTE
(
    SELECT 
        ROW_NUMBER() OVER(ORDER BY Id) AS CountRow,
        ProductId, 
        ProductName, 
        Tracklink, 
        ProductImage, 
        TrackPrice 
    FROM 
        ProductDetails
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.CountRow between 31 and 40

Upvotes: 3

user596075
user596075

Reputation:

You can't reference an alias in the WHERE clause. Wrap it in a subquery and you'll be fine:

select *
from
(
    SELECT ROW_NUMBER() OVER(ORDER BY Id) AS CountRow,
    ProductId, ProductName, Tracklink, ProductImage, TrackPrice FROM ProductDetails
) a
where CountRow between 31 and 40

Upvotes: 4

Related Questions