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.