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

Author

Topic

somsahi
Starting Member

23 Posts

Posted - 2011-10-16 : 05:35:26

question 1) order by company ,(date desc)
1)why "SONS" is above in order compraed to "SON"
2)wHY Alden is above ALDEN? THANKS FOR HELP

A.FINKL & SONS COMPANY 012945491 01/01/2010
A.FINKL & SONS COMPANY 012945491 01/01/2009
A.FINKL & SON 01/01/2009
A.FINKL & SON 01/01/2008
Alden Management Services 01/01/2010
ALDEN MANAGEMENT SERVICES 015253279 01/01/2010
ALDEN MANAGEMENT SERVICES 015253279 01/01/2009
________________________-
quetion 2
names are equal so as per desc order should be 8,6,5 while result is 6,5,8.This is problem for only this record .for all other result are in descending.any help?

MANAGEMENT SERVICES 12/01/2006
MANAGEMENT SERVICES 12/01/2005
MANAGEMENT SERVICES 01/01/2008

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-16 : 07:01:40

i think your databse collation is set to be case sensetive

you can overcome that convert.binary .You can use the CONVERT function to convert the data from character format to a string of hexadecimal values.

find the following link

http://support.microsoft.com/kb/171299

good luck

paul Tech

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 07:28:22

what does this return?


SELECT DATABASEPROPERTYEX('Your database name', 'Collation')

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

somsahi
Starting Member

23 Posts

Posted - 2011-10-16 : 08:06:28

sql latin1 general cp1 cl as

quote:
Originally posted by visakh16

what does this return?

SELECT DATABASEPROPERTYEX('Your database name', 'Collation')

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 13:52:52

then you're currently using a case insensitive collation.

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

somsahi
Starting Member

23 Posts

Posted - 2011-10-16 : 13:59:34

then what could be solution?

the

quote:
Originally posted by visakh16

then you're currently using a case insensitive collation.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 14:01:29

you're ordering also by date right. what are associated date values for them?

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

somsahi
Starting Member

23 Posts

Posted - 2011-10-16 : 14:23:47

actually it is conver varchar(10) date desc

values displayed as in question first post.

first on name and if name equal then date

quote:
Originally posted by visakh16

you're ordering also by date right. what are associated date values for them?

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-16 : 14:40:57

quote:
Originally posted by somsahi

then what could be solution?

Do you need a case sensitive collation? Typically when one is picked that's different than the default, there's a reason for it.

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

Subscribe to my blog

somsahi
Starting Member

23 Posts

Posted - 2011-10-16 : 14:48:12

i only want sorting on query level,cant change default collation
converion to binary is good if yes how?

quote:
Originally posted by tkizer

quote:
Originally posted by somsahi

then what could be solution?

Do you need a case sensitive collation? Typically when one is picked that's different than the default, there's a reason for it.

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

Subscribe to my blog

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-17 : 01:33:24

You can select a Cyrillic collation like:

order by STR collate Cyrillic_General_CI_AI

A full list of collations can be found with a call to:

select * from ::fn_helpcollations()

CS means Case Sensitive, AI means Accent Insensitive.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 01:36:36

quote:
Originally posted by somsahi

actually it is conver varchar(10) date desc

values displayed as in question first post.

first on name and if name equal then date

quote:
Originally posted by visakh16

you're ordering also by date right. what are associated date values for them?

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

why are you converting dates as varchar. if you want to get sorting based on date you need retain them as dates itself.

see illustration below


declare @tbl table
(
dateval datetime
)

insert @tbl
select '20100111' union all
select '20101008' union all
select '20110309' union all
select '20110123' union all
select '20100917' union all
select '20100806' union all
select '20091201'


--ordering date values as dates itself
select  convert(varchar(10),dateval,105) as [datesorted]
from @tbl
order by dateval


--ordering after converting to varchar
select  convert(varchar(10),dateval,105) as [stringsorted]
from @tbl
order by convert(varchar(10),dateval,105)



output
------------------------------------
datesorted
-----------------
01-12-2009
11-01-2010
06-08-2010
17-09-2010
08-10-2010
23-01-2011
09-03-2011


stringsorted
------------------
01-12-2009
06-08-2010
08-10-2010
09-03-2011
11-01-2010
17-09-2010
23-01-2011

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

somsahi
Starting Member

23 Posts

Posted - 2011-10-17 : 21:53:27

will check today and update by end of day.i think my code uing stringorted

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 01:44:43

ok. thats fine

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

somsahi
Starting Member

23 Posts

Posted - 2011-10-18 : 06:07:04

1)Collate not working
2)Used vishal solution(which is working in querymanager ) but when used in java then page is not diplayed.
in database effective date datatype is datetime

Actual query is
sql ="select distinct p.I_ProspectID, p.ProspectName, CONVERT(VARCHAR(10), pa.effectivedate, 101) AS [MM/DD/YYYY], " +

" pa.MarketOfficeName, p.Status, pa.SmartSuite, pa.UWID, pa.UASID, pa.RUM, pa.UWShop, UWDue, UASDue, pa.SalesMarket, pa.FundingType, p.isLockedPerm " +

" FROM dbo.EWS_Prospect p INNER JOIN" +

" dbo.EWS_RFP r ON p.I_ProspectId = r.I_ProspectID INNER JOIN" +

" dbo.EWS_Proposal pr ON r.I_RFPID = pr.I_RFPID INNER JOIN" +

" dbo.EWS_Package pa ON pr.I_ProposalID = pa.I_ParentProposalID " +

" where p.ProspectName like ? and p.GroupID like ? " +

uwLocSql + uwSql + uasSql + mktSql + agSql + ftSql + statusSql + effDtSql + sMktSql + asfSrchSql +

" order by p.ProspectName, CONVERT(VARCHAR(10), pa.effectivedate, 101) DESC";

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 07:49:12

why page is not displayed while used in java? is it throwing some exception?

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

somsahi
Starting Member

23 Posts

Posted - 2011-10-18 : 09:06:20

yes page not found 404.I have written query also.will be back after 3 hour.
but in querymanager it is working

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 09:44:54

that means somewhere its breaking in java code. can you step through and find exact place where it breaks in java?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources