Selecting in-between rows in SQL

Suppose you are writing a SQL query and you want to retrieve in-between records. That is, you have retrieved 100 records but you want from 40 to 50 (suppose), then writing a query becomes somewhat difficult. I had this problem while I was developing a web application in which I had to paginate the results. Here’s how to do it in MS-SQL.

Below is the query:

SELECT au_fname, au_lname, dob
FROM (SELECT ROW_NUMBER() OVER(ORDER BY au_lname ASC) AS NUM, * FROM authors) AS BetweenRows
WHERE NUM >= 40 AND NUM <= 50;

You can customize the query the way you want since you won’t be having the same column names as I have. Change the numeric values ’40’ and ’50’ to the records’ numbers. The above query will select all records between the 40th and the 50th records.


One thought on “Selecting in-between rows in SQL

Your Thoughts -

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s