CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
TCM
TCM

Reputation: 16920

Order By clause not sorting properly

This is one of the most interesting issue that I have come across in quiet some time. I have a scalar function defined in SQL server 2008 which whose return type is varchar(max)

This is the query:

 Select dbo.GetJurisdictionsSubscribed(u.UserID) as 'Jurisdiction' From Users u ORDER BY Jurisdiction desc

Could anybody explain why would AAAA... 2nd record in the resultset? I am doing a descending sort, AAA... should appear at the last. If I change the query to 

Jurisdiction asc

AAA goes 2nd last in the list instead of the 1st record.

This is the screenshot of the resultset: http://i48.tinypic.com/23j5vzq.jpg

Am I missing something?

Upvotes: 0

Views: 3012

Answers (4)

Ciarán
Ciarán

Reputation: 3057

It's a bit hard to tell on the screenshot, but can you check the length of the values because I think some of them have a leading space. If so then they would be sorted correctly.

Upvotes: 0

John Woo
John Woo

Reputation: 263923

notice the leading white spaces, try 

SELECT ...
FROM ...
ORDER BY LTRIM(Jurisdiction) desc

LTRIM would be fine.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294457

That is the correct sort order. You have spaces. You must read Case Sensitive Collation Order.

Upvotes: 4

dani herrera
dani herrera

Reputation: 51745

Because, as you can see in your screenshot, they are a white space in other rows before 'Wise' word (and withe space is greater than 'A')

You can left trim this spaces with:

ORDER BY ltrim( Jurisdiction ) desc

Upvotes: 3

Related Questions