sorting - 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

General SQL Server Forums

New to SQL Server Programming

sorting

Author

Topic

somsahi
Starting Member

23 Posts

Posted - 2011-10-16 : 00:30:00

hi,
sorting two column by query like this

order by employee,(date desc) ; desc is only on date

actual lit list is

Johnson 20/1/2008
JOHNSON 20/1/2007
emmy 20/1/2009
Emmy 20/1/2010

reult is

emmy 20/1/2009
Emmy 20/1/2010
JOHNSONS 20/1/2007
Johnson 20/1/2008

question
1)why emmy came before Emmy and JOHNONS came befre johnson

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-16 : 00:33:27

Do you have a case sensitive collation?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 00:33:45

the result posted is not for order by applied. as in result you've date sorted in ascending order and in order by you've specified descending. Emmy will come before Johnson as Emmy is alphabetically before Johnson..
order by employee,date desc means

it first sorted by employee
then within each employee records it sorts them by descending order of date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 00:35:29

yeah... if its case sensitive collation it will regard emmy and Emmy as different and also JOHNSON and johnson

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 00:40:29

this was the order i got when i used a case sensitive collation


declare @table table
(
var varchar(100)
)

insert into @table

select 'test' union all
select 'Test' union all
select 'TEST' union all
select 'TEst' 


SELECT var
FROM @table
ORDER BY var COLLATE SQL_Latin1_General_CP1_CS_AS ASC



output
-----------------------------------------------------------------
TEST
TEst
Test
test

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-16 : 06:31:45

you can change the collation of the table that you mad the order by

find the following link for that discussion

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77212

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-17 : 01:37:54

you can change the collation at the query level to get exact result

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources