SORT ORDER - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SORT ORDER

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-09-10 : 07:05:32

is there a way to sort in a way that 10 will come after 9 but not just after 1

I mean in this way
1
2
3
4
5
6
7
8
9
10

but not like this
1
10
2
3
4
5
6
7
8
9

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-09-10 : 07:09:12

The first one is a numeric sort, the second is an alphabetical sort. You can either convert this field in to a numeric datatype or do something like this:

ORDER BY CAST('00000000' + Field AS varchar(8))

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-10 : 07:10:25

Yes - use a numeric datatype (i.e. TINYINT, SMALLINT, INT, etc).
If you are storing these values in a VARCHAR/NVARCHAR field, you will have to convert them in your query:

SELECT MyTable.MyColumn
FROM MyTable
ORDER BY CONVERT(SMALLINT, MyTable.MyColumn)

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources