Inconsistent sort order using ORDER BY clause

I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
-
Non-A
Non-O
Noni
Nons

Resultset2
-
Non-A
Noni
Non-O
Nons

Any ideas?As far as i figured your query out, i am just wondering why this works for you as the 1 will be casted to a constant string which should not be allowed in the order by clause. Are you sure this works for you ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||First, you are running the release version of 2005. You should install at least SP1.

Second, if you install SP1, you would see an error:

Msg 408, Level 16, State 1, Line 9
A constant expression was encountered in the ORDER BY list, position 1.

Because you are sorting by the NUMBER 1, not column 1 by using the cast. So basically you have no sort.

Include column names in resultset - is it possible?

This is probably not a practical thing to do but I'd like to know if it
is possible
to include the column names as part of the resultset in my stored
procedure? The column
names could be the first row or and the last row of the resultset. The
number of columns
returned is unknown until runtime.
Thanks!
-SydneyAt no point down the line is there a client? Then who needs this data,
anyway...? :)
ML
http://milambda.blogspot.com/|||> The number of columns returned is unknown until runtime.
Why? How can that be? Please, explain.
If the structure of a result set depends on one or more conditions couldn't
you somehow figure out those conditions before executing this ellusive query
?
If you could, you'd know the structure of the result set in advance.
One other (mor logical) method would be to use a standard result set to
cover all options, and then if needed transform the result set after it's
returned from the procedure (or query) to more appropriately fit each
individual situation.
Sounds like sci-fi to me. But I'm willing to help.
ML
http://milambda.blogspot.com/|||ML, thanks for you reply.
See this link why I don't know # of columns in advance.
rl]
ML wrote:
> Why? How can that be? Please, explain.
> If the structure of a result set depends on one or more conditions couldn'
t
> you somehow figure out those conditions before executing this ellusive que
ry?
> If you could, you'd know the structure of the result set in advance.
> One other (mor logical) method would be to use a standard result set to
> cover all options, and then if needed transform the result set after it's
> returned from the procedure (or query) to more appropriately fit each
> individual situation.
> Sounds like sci-fi to me. But I'm willing to help.
>
> ML
> --
> [url]http://milambda.blogspot.com/" target="_blank">http://groups.google.com/group/micr...a.blogspot.com/|||Some people call this problem sci-fi, others call it a 'crosstab'.
And some people here think words are not important:)
You want to generate the columns dynamically.Now that
people know what you want I'm sure you will get a lot
of wonderful solutions.If you don't want to mess around with
them you can check out the RAC utility which will eliminate the
need to write any code on your part.It will generate your crosstab
magically.You will learn very little but you will keep your job:)
www.rac4sql.net|||Oh, it's a cross-tab query! Well, bring the normalized data to the client an
d
pivot it there. Or is there no client?
ML
http://milambda.blogspot.com/|||There is no client.Gotcha :)
www.rac4sql.net
"ML" <ML@.discussions.microsoft.com> wrote in message
news:AFFA52FB-C614-471D-91F7-E6D512AD5BC9@.microsoft.com...
> Oh, it's a cross-tab query! Well, bring the normalized data to the client
> and
> pivot it there. Or is there no client?
>
> ML
> --
> http://milambda.blogspot.com/