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.

Advertisements

One thought on “Selecting in-between rows in SQL

Your Thoughts -

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

WordPress.com Logo

You are commenting using your WordPress.com 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