SQL 2008 order by int field strange result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL 2008 order by int field strange result

Author

Topic

carriehoff
Starting Member

29 Posts

Posted - 2011-09-29 : 10:56:08

Hi all,

I have a simple query that returns different results when run against the same data on SQL 2000 vs. SQL 2008 when ordered by a field with type int.

select ID, position from tablename order by position

In SQL 2000 the results will be in this order

ID Position
5 1
4 2
6 1000
7 1000

and so on. The smaller numbers, such as 1 & 2 will be first in the list, but in SQL 2008, here is the result I will get running same query:

ID Position
6 1000
7 1000
5 1
4 2

The Position field is an int field. I would like to order the data from SQL 2008 to appear in the order that it is from SQL 2000 with the smaller numbers first.

Thank you,
Carrie

carriehoff
Starting Member

29 Posts

Posted - 2011-09-29 : 11:27:33

I actually have a few joins in my query, and upon further inspection, the table alias in the order by clause seemed to be causing the problem in this case.

I removed it, and now the table is ordering correctly.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources