How to do server side paging on data sets with spatial data?

Question

Hi

I have some code which allows me to do server side paging on my data:

DECLARE @PageSize int, @PageNumber int;
SET @PageSize = 6;
SET @PageNumber = 2;

WITH SelectedNewsItems AS
(SELECT
Title,
ROW_NUMBER () OVER (ORDER BY COUNT(*) DESC, NewsItemID) AS SelectedRowNumber
FROM NewsItems
GROUP BY
NewsItemID, Title)

SELECT * FROM SelectedNewsItems
WHERE SelectedRowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize

My problem arises when I also want to return the POINT geography data in my dataset:

WITH SelectedNewsItems AS
(SELECT
Title,
PositionPoint,
ROW_NUMBER () OVER (ORDER BY COUNT(*) DESC, NewsItemID) AS SelectedRowNumber
FROM NewsItems
GROUP BY
NewsItemID, Title, PositionPoint)

I then get the error:

"The type "geography" is not comparable. It cannot be used in the GROUP BY clause."

What shall I do to do server side paging, but also return spatial data?

---

rune007

Answer 1

Hi,

You may use the PK (assume NewsItemID is that one) in the query and join your table again to the CTE with the PK.

DECLARE @PageSize int, @PageNumber int;
SET @PageSize = 6;
SET @PageNumber = 2;

WITH SelectedNewsItems AS
(SELECT 
Title, 
NewsItemID,
ROW_NUMBER () OVER (ORDER BY COUNT(*) DESC, NewsItemID) AS SelectedRowNumber
FROM NewsItems 
GROUP BY
NewsItemID, Title)

SELECT B.* FROM SelectedNewsItems A
JOIN NewsItems B ON A.[NewsItemID] = B.[NewsItemID]
WHERE SelectedRowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize

Code has not been tested.

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 2

Thank you Mr. Janos Berke

I copy/pasted your query to SSMS and it seems to work just as it should :-)

---

rune007

Answer 3

my pleasure :)

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog