Dynamic order by

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.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.|||Pacific Fox (tacofleur@.gmail.com) writes:

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thats is excellent, I'm sure it will do better, thanks for that, will
give it a go.

Cheers.|||Pacific Fox wrote:

Quote:

Originally Posted by

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.