[SOLVED] SQL Query to select certain number of records with WHERE clause

SQL Query to select certain number of records with WHERE clause

Let's say I have a table that looks like the following:

ID | EntityType | Foo  | Bar
----------------------------
1  | Business   | test | test
2  | Family     | welp | testing
3  | Individual | hmm  | 100
4  | Family     | test | test
5  | Business   | welp | testing
6  | Individual | hmm  | 100

This table is fairly large, and there are random (fairly infrequent) instances of "Business" in the EntityType column.

A query like

SELECT TOP 500 * FROM Records WHERE EntityType='Business' ORDER BY ID DESC

works perfectly for grabbing the first set of Businesses, now how would I page backwards and get the previous set of 500 records which meet my criteria?

I understand I could look at records between IDs, but there is no guarantee on what ID that would be, for example it wouldn't just be the last ID of the previous query minus 500 because the Business EntityType is so infrequent.

I've also looked at some paging models but I'm not sure how I can integrate them while keeping my WHERE clause just how it is (only accepting EntityType of Business) and guaranteeing 500 records (I've used one that "pages" back 500 records, and only shows about 18 businesses because they're within the 500 total records returned).

Solution

select * from ( 
  select top 500 * from (
    select top 1000 * FROM Records WHERE EntityType='Business' ORDER BY ID DESC 
  ) x
  order by id 
) y 
order by id desc

Innermost query - take the top 1000, to get page 2 and page 1 results

2nd level query - take the page 2 records from the first query

outermost - reorder the results