order by - 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

Author

Topic

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-08-13 : 23:20:20

I want to order the following into an ascending order:

1st
2nd
3rd
4th
10th.

My issue is 10th always comes first on the list. Any help is appreciated.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-13 : 23:52:26

[code]DECLARE @tempfun TABLE(col1 varchar(10))
INSERT INTO @tempfun
SELECT '1st' UNION ALL
SELECT '2nd' UNION ALL
SELECT '3rd' UNION ALL
SELECT '4th' UNION ALL
SELECT '10aa' UNION ALL
SELECT '10th' UNION ALL
SELECT '11th'
SELECT *
FROM @tempfun
ORDER BY LEN(col1)[/code]

Hope can help...but advise to wait pros with confirmation...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 00:30:13

That won't work. See the query below:


DECLARE @tempfun TABLE(col1 varchar(10))
INSERT INTO @tempfun
SELECT	'10th' UNION ALL
SELECT	'1st' UNION ALL
SELECT	'4th' UNION ALL
SELECT	'2nd' UNION ALL
SELECT	'3rd' UNION ALL
SELECT	'11th'

SELECT	*
FROM	@tempfun
ORDER BY LEN(col1)

This will, assuming that there is a 2 character suffix on each record:


DECLARE @tempfun TABLE(col1 varchar(10))
INSERT INTO @tempfun
SELECT	'10th' UNION ALL
SELECT	'1st' UNION ALL
SELECT	'4th' UNION ALL
SELECT	'2nd' UNION ALL
SELECT	'3rd' UNION ALL
SELECT	'11th'

SELECT	*
FROM	@tempfun
ORDER BY Convert(int, Left(col1, len(col1) - 2))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-14 : 07:58:37

or

SELECT *
FROM @tempfun
ORDER BY left(col1,patindex('%[a-x]%',col1)-1)*1

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-14 : 08:00:10

quote:

Originally posted by waterduck

DECLARE @tempfun TABLE(col1 varchar(10))
INSERT INTO @tempfun
SELECT	'1st' UNION ALL
SELECT	'2nd' UNION ALL
SELECT	'3rd' UNION ALL
SELECT	'4th' UNION ALL
SELECT	'10aa' UNION ALL
SELECT	'10th' UNION ALL
SELECT	'11th'
SELECT	*
FROM	@tempfun
ORDER BY LEN(col1)

Hope can help...but advise to wait pros with confirmation...

select * FROM @tempfun
ORDER BY LEN(col1),col1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources