Order By, Sort Field - 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

Order By, Sort Field

Author

Topic

Christine19
Starting Member

7 Posts

Posted - 2012-09-26 : 01:56:11

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)

17689 Posts

Posted - 2012-09-26 : 02:21:16

assuming the format is fix, only the last segment might varies

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

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-09-26 : 02:30:40

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

Christine19
Starting Member

7 Posts

Posted - 2012-09-26 : 02:38:30

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! :)

Christine19
Starting Member

7 Posts

Posted - 2012-09-26 : 02:42:09

Hi senthil_nagore,

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

Problem Solved!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-10-05 : 05:19:59

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources