Every once and awhile life seems to go in a loop, and you wind up doing what you where doing a few years ago. This happened to me with a client that needed a web site running a MS SQL 2000 backend.
One of the requirements was to be able to display data while paging through records when sorted in a variety of ways. I’m sure many of you have done this either with a temporary table or ROWCOUNT (if not here is an article from 4guysfromrolla.com)
Since this web site needed to be sorted on items that were not unique (such as employeeID) but on things such as: room Type or City. A slightly different approach was needed.
From the 4guysfromrolla site, data paging is done on the employeeID, but I needed something that would work a column that was not unique
(
@startRowIndex int,
@maximumRows int
)
AS
DECLARE @first_id int, @startRow int
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that
-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows
SELECT e.*, d.name as DepartmentName FROM employees e INNER JOIN Departments D ON e.DepartmentID = d.DepartmentID WHERE employeeid >= @first_id ORDER BY e.EmployeeID
SET ROWCOUNT 0
GO
I altered the lines in green to:
SELECT @first_id = isnull(cityName,'zzzzLast') + '9' + right('000000000' + cast(rowID) as varchar(9)), 10) FROM employees ORDER BY isnull(cityName,'zzzzLast') + '9' + right('000000000' + cast(rowID) as varchar(9)), 10)
SELECT e.*, d.name as DepartmentName FROM employees e INNER JOIN Departments D ON e.DepartmentID = d.DepartmentID WHERE isnull(e.cityName,'zzzzLast') + '9' + right('000000000' + cast(rowID) as varchar(9)), 10) >= @first_id ORDER BY isnull(e.cityName,'zzzzLast') + '9' + right('000000000' + cast(rowID) as varchar(9)), 10)
As you can see I ensured that the column was unique and sortable by appending some leading Zeros & the rowID. Nothing to complex but it worked.
