Select a Range in MS SQL

Selecting a range from a table is easy in MYSQL. Say you want to bring back results that you are paginating. Bringing back thousands of results is inefficient and needless.

MS SQL there isn’t a simple way of doing this. The best way I have found, or my colleague Daniel has found, is to wrap a select statement inside another statement.

SELECT *
FROM t_Table WHERE strField="select"

Above is our full statement returning all our results. First we need to add a row number which will allow us to know the results position.

SELECT *, row_number()
OVER(ORDER BY lng_cust_unique_ID) as rn
FROM t_Table WHERE strField="select"

That is basically it, we now just enclose this statement within another, and specify which rows we’d like returned.

SELECT * FROM (
   SELECT *, row_number()
   OVER(ORDER BY lng_cust_unique_ID) as rn
   FROM t_Table WHERE strField="select"
) AS t WHERE t.rn > 40 AND t.rn < 50

Total Row Count
If you are using this for pagination, then you will need to know to total rows of this statement, without actually retrieving everything. You can do this by adding a simple addition shown below.

SELECT * FROM (
   SELECT *, row_number()
   OVER(ORDER BY lng_cust_unique_ID) as rn,
   COUNT(*) OVER() as rc
   FROM t_Table WHERE strField="select"
) AS t WHERE t.rn > 40 AND t.rn < 50