September 25, 2009 at 4:08 am
Does anybody have idea why code below doesn't work correctly with order by substring() ?
declare @t table (i char(1))
declare @x varchar(50)
insert into @t
select 'a'
union all
select 'b'
set @x = ''
select @x = @x + i
from @t
order by i desc
select @x as correct
set @x = ''
select @x = @x + i
from @t
order by substring(i, 1, 1) desc
select @x as incorrect
September 25, 2009 at 2:33 pm
AS per BOL,
order_by_expression
Specifies a column on which to sort. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list. Column names and aliases can be qualified by the table or view name. In Microsoft SQL Server 2005, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, it must be unique among all columns listed in the SELECT statement.
Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.
The ORDER BY clause can include items not appearing in the select list. However, if SELECT DISTINCT is specified, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.
Additionally, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.
I dont think u can use a function here!
---------------------------------------------------------------------------------
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply