Menu
Select id, name, ROW_NUMBER() OVER (ORDER BY id asc) as 'RowNo'
from customers
where RowNo between 50 AND 60
I am trying to select a subset of rows between 50 and 60 . The problem is 'RowNo' is an invalid column name.
Thank you
Using SQL SERVER 2008 R2
533
To fetch a subset of selected rows we use TOP clause fallowed by integer value. We can also use PERCENT clause to fetch n percent rows in result set. Notice that statement must have ORDER BY clause.
ROW_NUMBER function is a SQL ranking function that assigns a sequential rank number to each new record in a partition. When the SQL Server ROW NUMBER function detects two identical values in the same partition, it assigns different rank numbers to both.
The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.
You can use ROWNUM to limit the number of rows returned by a query, as in this example: SELECT * FROM employees WHERE ROWNUM < 10; If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed.
Use your query as subquery like bellow:
select * from (
Select id, name, ROW_NUMBER() OVER (ORDER BY id asc) as [RowNo]
from customers
) t
where RowNo between 50 AND 60
You can use CTE as well but whether to choose one over another read Difference between CTE and SubQuery? and check execution plan.
85
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
