Select subset of rows using Row_Number()

Question

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

Please don't use `'string delimiters'` for aliases. This syntax is non-standard, can be very confusing (e.g. `SELECT 'a' 'b'`), and is deprecated in SQL Server. Use `[square brackets]` (also not standard), or `"double quotes"` (standard), or no delimiters. For more info and lots of debate see https://sqlblog.org/blogs/aaron_bertrand/archive/2012/01/23/bad-habits-to-kick-using-as-instead-of-for-column-aliases.aspx — Aaron Bertrand, Feb 17 '12 at 13:15
P.S. the debate is about what you *should* use. There is little argument that single quotes *should not* be used. — Aaron Bertrand, Feb 17 '12 at 13:22
@Aaron: thnx, I wasn't sure if this was deprecated or not allowed in latest version. — ypercubeᵀᴹ, Feb 17 '12 at 13:33
@ypercube It still works in current versions but it does raise deprecated warning events (trace/perfmon). Well, only certain forms raise deprecated events but that's a different story. — Aaron Bertrand, Feb 17 '12 at 13:37

score 14 · Accepted Answer · edited May 23 '17 at 12:17

14

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.

edited May 23 '17 at 12:17

Community

1
1

answered Feb 17 '12 at 13:09

Michał Powaga

22,561
8
51
62

2

thank you! I don't know why you need to perform such a complicated query when in mysql you just use LIMIT 49, 10! can you please tell me why we had to write 't'? – test Feb 17 '12 at 13:11
2

@test: Because SQL-Server has no `LIMIT x OFFSET y`, like MySQL does. It has only `TOP n` which is the same as MySQL's `LIMIT n`. – ypercubeᵀᴹ Feb 17 '12 at 13:13
2

But with analytic functions that it does have, like the `ROWN_NUMBER()` and others, you can do much more complicated things (that are really hard to do in MySQL). – ypercubeᵀᴹ Feb 17 '12 at 13:15
1

@test SQL Server 2012 implements the *standard* method for paging (`OFFSET`/`FETCH`). Sure, MySQL is simple, but it's non-standard. I blogged about it here: https://sqlblog.org/blogs/aaron_bertrand/archive/2010/11/10/sql-server-11-denali-using-the-offset-clause.aspx – Aaron Bertrand Feb 17 '12 at 13:18
2

@test `t` is an alias for subquery without it you'll get an error (you can try it). More about it [Incorrect syntax near anything using subquery](http://stackoverflow.com/questions/4997132/sql-server-2008-mgmt-studio-incorrect-syntax-near-anything-using-subquery). – Michał Powaga Feb 17 '12 at 13:21
@Aaron: Come on, I like that SQL-Server implements (at last!) the standard for `FETCH n ROWS` but you can't argue that some other SQL-product is non-standard, implying not as good. `TOP n` is non-standard, too, and many SQL-Server users will not upgrade to 2012 version. – ypercubeᵀᴹ Feb 17 '12 at 13:24
@ypercube I'm all for proprietary extensions to the language, but if there's something already defined in the standard, I'd rather see that implemented than defining your own syntax. I have plenty of complaints about SQL Server's cowboy choices (like `TIMESTAMP`) but in this case MySQL did it wrong. – Aaron Bertrand Feb 17 '12 at 13:26
I certainly agree with that. But MySQL had that working long time ago. Not sure if the `FETCH` syntax was in the standard at the time that `LIMIT` and `TOP` were intoduced. – ypercubeᵀᴹ Feb 17 '12 at 13:29
Well, `TOP` isn't used just for paging, it actually has multiple uses already. – Aaron Bertrand Feb 17 '12 at 13:36

marc_s · Answer 2 · 2012-02-17T13:14:28.090

5

You need to do something like this:

;WITH PaginatingData AS
(
    Select id, name, ROW_NUMBER() OVER (ORDER BY id asc) as 'RowNo'
    from customers
)
SELECT *
FROM PaginatingData
where RowNo between 50 AND 60

Use a CTE (Common Table Expression - sort of an "inline view") as a "wrapper" so that your RowNo becomes a valid column name.

As an outlook - with SQL Server 2012, you'd be able to write something like this:

SELECT 
    id, name
FROM 
    dbo.customers
ORDER BY
    id
OFFSET 50 ROWS
FETCH NEXT 10 ROWS ONLY

SQL Server 2012 will have this ANSI SQL Standard compliant notation to do paging directly based on an ORDER BY clause. See this blog post (or tons of others) for more info and more samples.

edited Feb 17 '12 at 13:14

answered Feb 17 '12 at 13:08

marc_s

732,580
175
1,330
1,459

`AS RowNo` does not need quotes: `'RowNo'`, does it? – ypercubeᵀᴹ Feb 17 '12 at 13:11
@ypercube It doesn't, but it isn't wrong either. It's just less common syntax to see with T-SQL. – Yuck Feb 17 '12 at 13:12
@ypercube: I don't think there's absolutely necessary - I just left it as the OP had posted it. Personally, I would use the `RowNo = ROW_NUMBER().....` notation - but that's just personal style – marc_s Feb 17 '12 at 13:12
@ypercube, yuck and marc, please see my comment to the question (which I left before I saw these comments). I realize marc just copied the OP's code but while the syntax isn't wrong today it will be wrong in a future version of SQL Server. – Aaron Bertrand Feb 17 '12 at 13:17

Select subset of rows using Row_Number()

2 Answers2

Linked