I recently came across some client reports that, when initially run with search criteria, looked correct and displayed properly. However, that all changed once a user selected pagination.
The search results soon began to look like nothing I’d searched for, and after a few clicks of Next and Back I could produce errors. When I drilled into the code, I found that the results were ordered by date, but the pagination used the ID of the record. Using the record ID would have been fine if we were only displaying a dump of the data and ordering it by ID, but to have a functioning useful report, this did not work.
I needed a way to order by any report column header, and for the pagination to keep intact the ordered by results as a user moved between the pages. After some quick research I came across the SQL Server function ROW_NUMBER(), used in conjunction with OVER(). This was exactly what was needed to accomplish the report column sorting and the pagination honoring the order throughout the pages.
The result: Users were now able to paginate results, as well as sort by column heading, making for a logical display.
Have you found any tips or tricks for search results?