Menu
How would you get the nth row (i.e 5th row) from the result of a query in SQL Server?
246
ROW_NUMBER (Window Function) ROW_NUMBER (Window Function) is a standard way of selecting the nth row of a table. It is supported by all the major databases like MySQL, SQL Server, Oracle, PostgreSQL, SQLite, etc.
For SQL Server, a generic way to go by row number is as such: SET ROWCOUNT @row --@row = the row number you wish to work on.
Here's the SQL query to select every nth row in MySQL. mysql> select * from table_name where table_name.id mod n = 0; In the above query, we basically select every row whose id mod n value evaluates to zero.
SQL Server 2005 and newer:
with Records AS(select row_number() over(order by datecreated) as 'row', * from Table) select * from records where row=5 You can change the order by to determine how you sort the data to get the fifth row.
Tested on my local install: Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )
170
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
