Help with Sort ID differences

Hi. I was wondering what is the default differences in the following 2 sort
order ids:
51 - SQL_Latin1_General_CP1_CS_AS collation.
71 - Latin1_General_CS_AS collation.
As some of you may have guess, the same SQL code returns different results
sets from two servers that are nearly the same with the execption being, the
Server's Sort ID.
Thanks,
JoeJoe,
The sort order will be differant for unicode data as well as ordinary string
data as windows and sql collations are slightly differant. Microsoft
describe the sql_ collations nicely in this article
http://support.microsoft.com/?id=322112.
This also points you to some examples of where the sort orders differ
e.g. a-c and ab
where the - is sorted differantly between the two collations
Chris
"Joe D" wrote:
> Hi. I was wondering what is the default differences in the following 2 sort
> order ids:
> 51 - SQL_Latin1_General_CP1_CS_AS collation.
> 71 - Latin1_General_CS_AS collation.
> As some of you may have guess, the same SQL code returns different results
> sets from two servers that are nearly the same with the execption being, the
> Server's Sort ID.
> Thanks,
> Joe
>
>|||Hi Chris,
Thanks for the pointer. I'll check it out.
Joe
"Chris Hoare" <choare@.nospam.nospam> wrote in message
news:2A1473C4-760B-4D2A-8421-1739AF812F45@.microsoft.com...
> Joe,
> The sort order will be differant for unicode data as well as ordinary
> string
> data as windows and sql collations are slightly differant. Microsoft
> describe the sql_ collations nicely in this article
> http://support.microsoft.com/?id=322112.
> This also points you to some examples of where the sort orders differ
> e.g. a-c and ab
> where the - is sorted differantly between the two collations
> Chris
> "Joe D" wrote:
>> Hi. I was wondering what is the default differences in the following 2
>> sort
>> order ids:
>> 51 - SQL_Latin1_General_CP1_CS_AS collation.
>> 71 - Latin1_General_CS_AS collation.
>> As some of you may have guess, the same SQL code returns different
>> results
>> sets from two servers that are nearly the same with the execption being,
>> the
>> Server's Sort ID.
>> Thanks,
>> Joe
>>

Help with Sort ID differences

Hi. I was wondering what is the default differences in the following 2 sort
order ids:
51 - SQL_Latin1_General_CP1_CS_AS collation.
71 - Latin1_General_CS_AS collation.
As some of you may have guess, the same SQL code returns different results
sets from two servers that are nearly the same with the execption being, the
Server's Sort ID.
Thanks,
JoeJoe,
The sort order will be differant for unicode data as well as ordinary string
data as windows and sql collations are slightly differant. Microsoft
describe the sql_ collations nicely in this article
http://support.microsoft.com/?id=322112.
This also points you to some examples of where the sort orders differ
e.g. a-c and ab
where the - is sorted differantly between the two collations
Chris
"Joe D" wrote:

> Hi. I was wondering what is the default differences in the following 2 sor
t
> order ids:
> 51 - SQL_Latin1_General_CP1_CS_AS collation.
> 71 - Latin1_General_CS_AS collation.
> As some of you may have guess, the same SQL code returns different result
s
> sets from two servers that are nearly the same with the execption being, t
he
> Server's Sort ID.
> Thanks,
> Joe
>
>|||Hi Chris,
Thanks for the pointer. I'll check it out.
Joe
"Chris Hoare" <choare@.nospam.nospam> wrote in message
news:2A1473C4-760B-4D2A-8421-1739AF812F45@.microsoft.com...[vbcol=seagreen]
> Joe,
> The sort order will be differant for unicode data as well as ordinary
> string
> data as windows and sql collations are slightly differant. Microsoft
> describe the sql_ collations nicely in this article
> http://support.microsoft.com/?id=322112.
> This also points you to some examples of where the sort orders differ
> e.g. a-c and ab
> where the - is sorted differantly between the two collations
> Chris
> "Joe D" wrote:
>sql

help with sort

I have a column which has values as
1
2
2.1
3
4
2.1.1
2.1.1.1
2.1.2
2.1.3
2.2
2.1.4
2.3
2.99
2.99.1
2.1.5
2.99.1.1
2.1.6
2.100
2.100.1
2.101
2.102
2.1.7
and i need to get this sorted as
1
2
2.1
2.1.1
2.1.1.1
2.1.2
2.1.3
2.1.4
2.1.5
2.1.6
2.1.7
2.2
2.99
2.99.1
2.99.1.1
2.100
2.100.1
2.101
2.102
Is this comething doable thru sql. any help will be greatly appreciated.
Thanks
Message posted via http://www.webservertalk.com>> Is this comething doable thru sql.
Are these IP addresses or something? Is it always less than or equal to 4
digits in the string? If such sorting is a business requirement, you might
want to consider a better schema. You post somewhat exemplifies the
complexity behind queries involving tables with multiple values crammed in a
single column.
One quick & dirty way is to do:
SELECT col
FROM tbl
ORDER BY CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 4 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 3 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 2 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 1 ) AS INT );
You can find details about PARSENAME and REPLICATE functions in SQL Server
Books Online. The idea is a extract each component ( as identified by a
dot ) of the string and then sort them accordingly. The type conversion to
INT is to avoid character based sorting which is not what is specified in
your sample results.
Anith

HELP WITH RANKING !( please)

Hello Everybody:

Somebody know how I can create a nested ranking from a analysis services
cube, using MDX?.. i mean, sort a sales ranking that show .how % of my
clients....b.buy only 100 articles......how many clients ...expressed in %
, buy just 200 articles parent level using a measure and then sort
a child level using the same measure... something like:

I got it nex table :
Location client_id Sales article vendor_id
city 1 122230 100 01
City1 122231 200 05
City1 122232 500 02
City1 122233 100 04
City 2 122234 100 02
City2 122235 100 02
City2 122236 200 03
City3 122237 300 01
City3 122238 400 01
City3 122239 200 03

I want show this : 40% clients . Buy 100 articles
30% clients . Buy 200 articles
10% clients .Buy 300 articles
10% clients . Buy 400 articles
10% clients . Buy 500 articles

or this : the 70 % of clients buy between 100 and 200 aticles !!

i.m use sql server 2000 , analysis services 8.0 ,olap , and mdx

PLEASE HELP ME MADE THIS!!!!

write me too at lealy_lenn@.hotmail.com

--
hi all !

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1Use this logic

declare @.t table(i int)
insert into @.t values(100)
insert into @.t values(200)
insert into @.t values(300)
insert into @.t values(100)
insert into @.t values(400)
insert into @.t values(100)
insert into @.t values(300)
insert into @.t values(600)
insert into @.t values(200)
insert into @.t values(600)
select i, s*100/Total as percentage from (
select i,count(i) as s ,(select count(i) from @.t) as total from @.t
group by i) T

Madhivanan|||Heathon via SQLMonster.com wrote:
> Hello Everybody:
> Somebody know how I can create a nested ranking from a analysis services
> cube, using MDX?.. i mean, sort a sales ranking that show .how % of my
> clients....b.buy only 100 articles......how many clients ...expressed in %
> , buy just 200 articles parent level using a measure and then sort
> a child level using the same measure... something like:
> I got it nex table :
> Location client_id Sales article vendor_id
> city 1 122230 100 01
> City1 122231 200 05
> City1 122232 500 02
> City1 122233 100 04
> City 2 122234 100 02
> City2 122235 100 02
> City2 122236 200 03
> City3 122237 300 01
> City3 122238 400 01
> City3 122239 200 03
>
> I want show this : 40% clients . Buy 100 articles
> 30% clients . Buy 200 articles
> 10% clients .Buy 300 articles
> 10% clients . Buy 400 articles
> 10% clients . Buy 500 articles
>
> or this : the 70 % of clients buy between 100 and 200 aticles !!
> i.m use sql server 2000 , analysis services 8.0 ,olap , and mdx
> PLEASE HELP ME MADE THIS!!!!
> write me too at lealy_lenn@.hotmail.com

You might get a better response in microsoft.public.sqlserver.olap

Simon

help with Interactive Sort on a column...

I have been selecting the column name textbox at the top of a column
with an int value in sql server.
When I go to Interactive Sort/Sort Expression, I just put the same
expression as the one below the column name in the details "=Fields!
qtySold.Value".
The results is not what I was hoping for:
981
90
9
876
800
8
777
76
7
Instead of:
981
876
800
777
90
76
9
8
7
Any help is appreciated.
Thanks,
Trinttrint wrote:
> I have been selecting the column name textbox at the top of a column
> with an int value in sql server.
> When I go to Interactive Sort/Sort Expression, I just put the same
> expression as the one below the column name in the details "=Fields!
> qtySold.Value".
> The results is not what I was hoping for:
> 981
> 90
> 9
> 876
> 800
> 8
> 777
> 76
> 7
> Instead of:
> 981
> 876
> 800
> 777
> 90
> 76
> 9
> 8
> 7
> Any help is appreciated.
> Thanks,
> Trint
Just a guess, but it looks like it's sorting it as if it's text and not
a number. Hope I'm not pointing out the obvious. Have you tried
converting the value that you're sorting on to an integer explicitly?
James
--