RowNumber Order By Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

RowNumber Order By Problem

Author

Topic

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2009-02-17 : 20:46:14

Hi

I am using sql 2008 and I am working out the distance of customers from a location that is enetered by the user. Once the user has entered a PostCode I then use lat and longs to work out the distance via a UDF which is in my select. However I also only want improve performance by paging results in rows of 10's.

The problem is that I want to sort my results by shortest to longest in Miles. So for example if my query find 100 customers wihin my area I would like to return the 10 nearest first. Below is example of my sql statement

WITH Customers AS
(
SELECT TOP 100 PERCENT CustomerName, (dbo.fnCircleDistance(CustomerLatitude, CustomerLongitude, @prmUserLatitude, @prmUserLongitude)) AS Miles, ,ROW_NUMBER() over(order by CustomerName) as RowNumber
FROM Customers
ORDER BY Miles desc
)
SELECT * from Customers
ORDER BY Miles

The above will only sort the 10 results that are being paged by Miles. Ideally I want to put "Miles" in the RowNumber order by statement but I cannot do this as the value is created by the UDF.

Any ideas how I can get round this?

Many thanks inadvance

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2009-02-17 : 20:49:37

Opps I forgot to include the RouNumber filter in the 2nd select statment, so it should read:

WITH Customers AS
(
SELECT TOP 100 PERCENT CustomerName, (dbo.fnCircleDistance(CustomerLatitude, CustomerLongitude, @prmUserLatitude, @prmUserLongitude)) AS Miles, ,ROW_NUMBER() over(order by CustomerName) as RowNumber
FROM Customers
ORDER BY Miles desc
)
SELECT * from Customers
WHERE RowNumber BETWEEN (@prmCurrentPage - 1) * @prmPageSize + 1 AND @prmCurrentPage * @prmPageSize
ORDER BY Miles

Many thanks

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2009-02-17 : 23:06:50

If I cant get round the above problem, the only solution I can think of is to remove the RowNumber paging method. However how much of performance will i loose? My result set could be as big as 4000 rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 23:57:07

do you mean this?


WITH Customers AS
(
SELECT TOP 100 PERCENT 
FROM ROW_NUMBER() over(order by Miles) as RowNumber,
CustomerName,
Miles
FROM
(
SELECT CustomerName, (dbo.fnCircleDistance(CustomerLatitude, CustomerLongitude, @prmUserLatitude, @prmUserLongitude)) AS Miles
FROM Customers
)t

ORDER BY Miles desc
)
SELECT * from Customers
WHERE RowNumber BETWEEN (@prmCurrentPage - 1) * @prmPageSize + 1 AND @prmCurrentPage * @prmPageSize
ORDER BY Miles

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2009-02-18 : 11:11:37

visakh16 you are a star! thank you so much.

I have to say that this is the best forum I have ever come across

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 11:52:12

you're Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources