Diffirent Order by??!! - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Diffirent Order by??!!

Author

Topic

roy_kty
Starting Member

29 Posts

Posted - 2006-11-15 : 22:48:35

Dear all,

As I try to compare the data in MySQL and MSSQL table,
I use query 'SELECT * FROM table ORDER BY id'
However I get different sequence result as MSSQL sorting is different to MySQL

MSSQL result:
id
SCM102
SCM-1-E1
SCM202
SCM-2-E1
SCM302
SCM-3-E1

MSSQL result:
id
SCM-1-E1
SCM-2-E1
SCM-3-E1
SCM102
SCM202
SCM302

How can I sort them into the same sequnce?
Thx for your. answer

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 22:56:04

Set the same collations on each table/database.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-15 : 23:03:53

Don't rely on database's sorting order when you are using multiple database systems. Instead add your own order column to the table and order based on that column.

For e.g.

declare @a table
(
	a varchar(100),
	orderid int
)

insert @a
select 'SCM102', 1 union all
select 'SCM-1-E1', 2  union all
select 'SCM202', 3 union all
select 'SCM-2-E1', 4 union all
select 'SCM302', 5 union all
select 'SCM-3-E1', 6 

select * from @a order by orderid

This way you can ensure that order will always be the same, no matter which RDBMS you use.

Harsh Athalye
India.
"Nothing is Impossible"

roy_kty
Starting Member

29 Posts

Posted - 2006-11-15 : 23:24:06

Thx for the answers
May I ask how to set the collations??
As I don't want to create a temp table to increase the memory loading

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 23:27:41

Look for "collations" in Books Online and the MySQL docs.

roy_kty
Starting Member

29 Posts

Posted - 2006-11-15 : 23:48:51

I have tried but it alert me that
The object 'PK_id' is dependent on column 'id'.
Seem can not change primary key collations

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-15 : 23:56:27

What is the datatype of id? If it is varchar, order by wont give proper result as you want

Madhivanan

Failing to plan is Planning to fail

roy_kty
Starting Member

29 Posts

Posted - 2006-11-16 : 00:50:32

the type is nvarchar
So I need to change type ??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 00:52:54

quote:
Originally posted by roy_kty

the type is nvarchar
So I need to change type ??

No you dont need to change
Post some sample data and expected result

Madhivanan

Failing to plan is Planning to fail

roy_kty
Starting Member

29 Posts

Posted - 2006-11-16 : 01:09:15

Smaple Data:

MSSQL result:
id
SCM102
SCM-1-E1
SCM202
SCM-2-E1
SCM302
SCM-3-E1

MYSQL result:
id
SCM-1-E1
SCM-2-E1
SCM-3-E1
SCM102
SCM202
SCM302

I expected the result of MSSQL can as same as MySQL result

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-16 : 01:33:10

Do you have any reason to use nvarchar instead of varchar?If no, then use Varchar otherwise use following query:

declare @t table
(
	a nvarchar(100)
)

insert @t
select 'SCM102' union all
select 'SCM-1-E1' union all
select 'SCM202' union all
select 'SCM-2-E1' union all
select 'SCM302' union all
select 'SCM-3-E1'


select * from @t
order by convert(varchar(100),a)

Note: Conversion from nvarchar to varchar may result in loss of data if you are using unicode characters.

Harsh Athalye
India.
"Nothing is Impossible"

cijuvarghese
Starting Member

12 Posts

Posted - 2006-11-16 : 01:34:11

Hi,
i checked the same by doing the sample in MSSQL. but i got the result same as MYSQL. see the query.

declare @temtable table(myid varchar(50))
insert into @temtable(myid) values ('SCM102')
insert into @temtable(myid) values ('SCM-1-E1')
insert into @temtable(myid) values ('SCM202')
insert into @temtable(myid) values ('SCM-2-E1')
insert into @temtable(myid) values ('SCM302')
insert into @temtable(myid) values ('SCM-3-E1')
select * from @temtable order by 1
RESULTSET
-----------
SCM-1-E1
SCM-2-E1
SCM-3-E1
SCM102
SCM202
SCM302

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-16 : 01:35:31

Because you used varchar, not nvarchar.

Harsh Athalye
India.
"Nothing is Impossible"

cijuvarghese
Starting Member

12 Posts

Posted - 2006-11-16 : 01:35:39

Hi,
One thing. its because of the datatype NVARCHAR. if u can change the datatype as VARCHAR, then the result will be same.
Regards,
Ciju

cijuvarghese
Starting Member

12 Posts

Posted - 2006-11-16 : 01:38:05

Otherwise do a casting while selecting the data. that will solve the problem.
Ciju

roy_kty
Starting Member

29 Posts

Posted - 2006-11-16 : 02:03:17

Seems not relate to nvarchar and varchar
I convert to varchar but have the result as same as before
Is that relate to the setting??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 02:04:53

I tried this code on SQL2000

declare @temtable table(myid varchar(50))

insert into @temtable(myid) values ('SCM102')
insert into @temtable(myid) values ('SCM-1-E1')
insert into @temtable(myid) values ('SCM202')
insert into @temtable(myid) values ('SCM-2-E1')
insert into @temtable(myid) values ('SCM302')
insert into @temtable(myid) values ('SCM-3-E1')

select * from @temtable order by 1

and got this resultset

SCM102
SCM-1-E1
SCM202
SCM-2-E1
SCM302
SCM-3-E1

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-16 : 02:06:39

quote:
Originally posted by roy_kty

Seems not relate to nvarchar and varchar
I convert to varchar but have the result as same as before
Is that relate to the setting??

What is the collation of your database?

Select databasepropertyex('db1', 'collation')

Harsh Athalye
India.
"Nothing is Impossible"

roy_kty
Starting Member

29 Posts

Posted - 2006-11-16 : 02:16:39

Chinese_Taiwan_Stroke_CI_AS for table
I have tried to convert the column collation but as its a primary key
it alert
The object 'PK_id' is dependent on column 'id'.

roy_kty
Starting Member

29 Posts

Posted - 2006-11-16 : 02:22:45

I used
ALTER TABLE table ALTER COLUMN id varchar(50) COLLATE Latin1_General_CI_AS NOT NULL

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-16 : 02:30:29

No need to alter the table. Try this:

declare @t table
(
	a nvarchar(100) collate Chinese_Taiwan_Stroke_CI_AS
)

insert @t
select 'SCM102' union all
select 'SCM-1-E1' union all
select 'SCM202' union all
select 'SCM-2-E1' union all
select 'SCM302' union all
select 'SCM-3-E1'

select * from @t
order by convert(varchar(100),a) collate SQL_Latin1_General_CP1_CI_AS

Harsh Athalye
India.
"Nothing is Impossible"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources