row_number and variable for sorting
-
Hi,
Does anyone know why this does not work? I'm using the Pubs database in SQL2k5 for the example:
declare @sortExpression varchar(32)
set @sortExpression = 'job_desc'
select
*,
row_number() over( order by case @sortExpression
when 'job_desc' then job_desc
else job_id end ) as rowNum
from
jobs
Msg 245, Level 16, State 1, Line 14
Conversion failed when converting the varchar value 'New Hire - Job not specified' to data type smallint.
But this does work:
declare @sortExpression varchar(32)
set @sortExpression = 'job_desc'
select
*,
row_number() over( order by case @sortExpression
when 'job_desc' then job_desc end ) as rowNum
from
jobs
Thank you very much!!!
-
you must cast all sorting columns to the same data type.
try changing :
when 'job_desc' then job_desc
else job_id end ) as rowNum
to
when 'job_desc' then job_desc
else cast (job_id as varchar(20)) end ) as rowNum
warning: that sort is working with characters you may want to produce leading zeros
Cheers,
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply