SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Order By, Sort Field		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Christine19
Starting Member

Philippines
7 Posts
Posted - 09/26/2012 :  01:56:11  Show Profile  Reply with Quote
Hi, i am new in this forum and also in SQL Database. I have a problem in sorting my record.

My query is -> SELECT accessionno FROM tblBooks ORDER BY accessionno

and this is my output:

2012-C1-6
2012-C2-7
2012-C3-8
2012-C4-9
2012-C5-10
2012-C1-1
2012-C2-2
2012-C3-3
2012-C4-4
2012-C5-5

How can i Sort like this:

2012-C1-1
2012-C2-2
2012-C3-3
2012-C4-4
2012-C5-5
2012-C1-6
2012-C2-7
2012-C3-8
2012-C4-9
2012-C5-10
?

I tried searching it on google but no luck finding near idea or answer in my problem.

Pls help. Thanks in advance.

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 09/26/2012 :  02:21:16  Show Profile  Reply with Quote
assuming the format is fix, only the last segment might varies

ORDER BY LEFT(accessionno , 7), convert(int, substring(accessionno, 9, 2))


KH
Time is always against us
Go to Top of Page

senthil_nagore
Aged Yak Warrior

India
997 Posts
Posted - 09/26/2012 :  02:30:40  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
for varaible format

Select accessionno from tblBooks order by cast(reverse(left(reverse(accessionno ),charindex('-',reverse(accessionno ),0)-1)) as int)

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

Christine19
Starting Member

Philippines
7 Posts
Posted - 09/26/2012 :  02:38:30  Show Profile  Reply with Quote
Hi khtan,

Thank you very very much!!!!!!! I am very grateful that you helped me :)
your solution is working..

SELECT accessionno,booktitle FROM tblBooks ORDER BY convert(int, substring(accessionno, 9, 2))

and this is my output now:
Accessionno Booktitle
2012-C1-1 Physics
2012-C2-2 Physics
2012-C3-3 Physics
2012-C4-4 Physics
2012-C5-5 Physics
2012-C1-6 Chemistry
2012-C2-7 Chemistry
2012-C3-8 Chemistry
2012-C4-9 Chemistry
2012-C5-10 Chemistry

Thanks again! :)
Go to Top of Page

Christine19
Starting Member

Philippines
7 Posts
Posted - 09/26/2012 :  02:42:09  Show Profile  Reply with Quote
Hi senthil_nagore,

Thank you very much for the reply.. :) Your solution is also works great!! :)

Problem Solved!!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 10/05/2012 :  05:19:59  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
If the format ix fixed, you can try this too


Select accessionno from tblBooks
order by parsename(replace(accessionno ,'-','.'),1)*1


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000