Dynamic order by

[Pacific Fox]

I am using a dynamic order by statement;

ORDER BY CASE @sort
WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK,
0 ) AS CHAR( 5 ) )
WHEN 1 THEN C.title
WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance]
( @fromLatitude, @fromLongitude, L.latitude, L.longitude ) ) AS
CHAR( 9 ) )
WHEN 3 THEN ( C.locality + ' ' + C.state )
WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC

The problem is with the numeric values, I have to cast them as a
string, but in the results 114km
obviously is not between 1137km and 1144km.

Anyone any ideas on this?
Thanks in advance.

[Pacific Fox]

Got this one sorted, I am padding the string with zeros. I think it is
affecting the execution time drastically though (talking about 500,000
records). Will do some more reseach, any better suggestions would be
appreciated.

[Erland Sommarskog]

Pacific Fox (ta*******@gmail.com) writes:

I am using a dynamic order by statement;

ORDER BY CASE @sort
WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK,
0 ) AS CHAR( 5 ) )
WHEN 1 THEN C.title
WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance]
( @fromLatitude, @fromLongitude, L.latitude, L.longitude ) ) AS
CHAR( 9 ) )
WHEN 3 THEN ( C.locality + ' ' + C.state )
WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC

The problem is with the numeric values, I have to cast them as a
string, but in the results 114km
obviously is not between 1137km and 1144km.

I saw that you resolved the problem, but wanted alternative solutions.

One is to do:

ORDER BY CASE @Sort
WHEN 0 THEN coalesce(t2. ....
WHEN 2 THEN ceiling ...
WHEN 4 THEN C.Price
END,
CASE @Sort
WHEN 1 THEN C.Title
WHEN 3 THEN C.locality ...
END

That is, have one case expression per type. If Price is not integer,
but decimal or float, that should maybe be a third branch, to avoid
conversion for the integer choices.

I can't say off-hand how this will work performancewise.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Pacific Fox]

Thats is excellent, I'm sure it will do better, thanks for that, will
give it a go.

Cheers.

[Ed Murphy]

Pacific Fox wrote:

I am using a dynamic order by statement;

ORDER BY CASE @sort
WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK,
0 ) AS CHAR( 5 ) )
WHEN 1 THEN C.title
WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance]
( @fromLatitude, @fromLongitude, L.latitude, L.longitude ) ) AS
CHAR( 9 ) )
WHEN 3 THEN ( C.locality + ' ' + C.state )
WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC

The problem is with the numeric values, I have to cast them as a
string, but in the results 114km
obviously is not between 1137km and 1144km.

Adapted from a workaround recently posted by Erland:

ORDER BY
CASE @sort WHEN 0 THEN t2.RANK END,
CASE @sort WHEN 0 THEN t3.RANK END,
CASE @sort WHEN 1 THEN C.title END,
CASE @sort WHEN 2 THEN CEILING( [dbo].[fn_calculateDistance]
( @fromLatitude, @fromLongitude, L.latitude, L.longitude ) ) END,
CASE @sort WHEN 3 THEN C.locality + ' ' + C.state END,
CASE @sort WHEN 4 THEN C.price END

For instance, when @sort = 4, then all formulas except
CASE @sort WHEN 4 THEN C.price END
return NULL and thus have no effect on the sort order.