Pagination with ColdFusion and MSSQL (faux MySQL’s LIMIT X,Y)

Pagination is something that has always been somewhat difficult for me. Especially optimizing it for large sites. To make it even harder, the customer wants the first page to show three items, and every subsequent page to show ten. Not hard enough? OK, they want page number links instead of simple left/right arrow links, such as:

1 2 3 [4] Next »

Still Easy?

• Only show 4 page links at a time
• When you click ‘next’, show the next block of 4 pages
• When you click ‘prev’, show the previous block of 4 pages
• If you are on the first block of links, do not show the ‘prev’ link
• If you run out of records, only show the correct number of page links in that block
• If you run out of records, do not show the ‘next’ link

Remember, page 1 has 3 items. Page 2 has 10. So the math gets kinda tricky when you don’t want to do ‘what page are we on’ statements all over the place.

Here is my solution. All you have to do is enter your records per page (”allperpage”), number of page links at a time (”blocksof”), and the number of items on the first page (”firstperpage”).


The SQL query has been optimized to get as close to MySQL’s ‘LIMIT’ function as possible. We’re doing an INNER JOIN here, and sorting on columns from both tables. I haven’t seen any examples of this optimization technique with an inner join, or even sorting on more than one column. We pull back the max rows we’d need, then trim the top with a reverse sort, finally un-reverse the sort for the web page. WHEW.

On to the code: Pagination with ColdFusion and MSSQL (faux MySQL’s LIMIT X,Y)