Okay so here's my issue.
The user can go onto my site and retrieve 8 records at a time, then he/she is given the option to load more. These 8 records can be sorted by a param passed into the proc. Now when I get these 8 records on the front end, I have their ID's (hidden to the user though obviously), but their ID's are not in any specific order because the records are sorted by a variety of possible things.
When they click "Load More", I should be able to get the next 8 records from the database, sorted in the SAME fashion as the first 8 were.
For example, "Give me the top 8 records sorted by age". -> Click Load More -> Give me the next 8 oldest records without showing me the onces I just saw.
How can I call the proc and make sure none from the first result set are returned though? I only want to return 8 records at a time for efficiency reasons.
SELECT TOP 8
m.message,
m.votes,
(geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 as distance,
m.location,
datediff(hour,m.timestamp, getdate()) as age,
m.messageId,
ml.voted,
ml.flagged
FROM
tblMessages m
left join tblIPMessageLink ml on m.messageid = ml.messageid
WHERE
m.timestamp >= DATEADD(day, DATEDIFF(day, 0, @date), 0)
and
m.timestamp < DATEADD(day, DATEDIFF(day, 0, @date), 1)
ORDER BY
CASE WHEN @sort = 'votes1' THEN m.votes END DESC,
CASE WHEN @sort = 'votes2' THEN m.votes END ASC,
CASE WHEN @sort = 'age1' THEN datediff(hour,m.timestamp, getdate()) END ASC,
CASE WHEN @sort = 'age2' THEN datediff(hour,m.timestamp, getdate()) END DESC,
CASE WHEN @sort = 'distance1' THEN (geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 END ASC,
CASE WHEN @sort = 'distance2' THEN (geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 END DESC
END
That's my current query. How would I change it to work with paging?
use row_number
example
call 1
;WITH cte AS(SELECT *,row_number() OVER( ORDER BY name) AS rows FROM sysobjects)
SELECT * FROM cte WHERE ROWS BETWEEN 1 AND 8
ORDER BY rows
call 2
;WITH cte AS(SELECT *,row_number() OVER( ORDER BY name) AS rows FROM sysobjects)
SELECT * FROM cte WHERE ROWS BETWEEN 9 AND 16
ORDER BY rows
of course you want to use parameters instead of hardcoding the numbers, this way you can reuse the query, if the column can be sorted arbitrarily then you might need to use dynamic SQL
edit, here is what it should look like, you probably also want to return the max rownumber so that you know how many rows can be potentially returned also you can make rows per page dynamic, in that case it would be something like
where Rows between @StartRow and (@StartRow + @RowsPerPage) -1
make sure to read Dynamic Search Conditions in T-SQL Version for SQL 2008 to see how you can optimize this to get plan reuse and a better plan in general
anyway, here is the proc, untested of course since I can't run it here
DECLARE @StartRow INT,@EndRow INT
--SELECT @StartRow =1, @EndRow = 8
;WITH cte AS (SELECT ROW_NUMBER() OVER (ORDER BY
CASE WHEN @sort = 'votes1' THEN m.votes END DESC,
CASE WHEN @sort = 'votes2' THEN m.votes END ASC,
CASE WHEN @sort = 'age1' THEN datediff(hour,m.timestamp, getdate()) END ASC,
CASE WHEN @sort = 'age2' THEN datediff(hour,m.timestamp, getdate()) END DESC,
CASE WHEN @sort = 'distance1' THEN (geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 END ASC,
CASE WHEN @sort = 'distance2' THEN (geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 END DESC
END) AS rows
m.message,
m.votes,
(geography::Point(@latitude, @longitude, 4326).STDistance(m.point)) * 0.000621371192237334 as distance,
m.location,
datediff(hour,m.timestamp, getdate()) as age,
m.messageId,
ml.voted,
ml.flagged
FROM
tblMessages m
left join tblIPMessageLink ml on m.messageid = ml.messageid
WHERE
m.timestamp >= DATEADD(day, DATEDIFF(day, 0, @date), 0)
and
m.timestamp < DATEADD(day, DATEDIFF(day, 0, @date), 1)
)
SELECT *
FROM cte WHERE ROWS BETWEEN @StartRow AND @EndRow
ORDER BY rows