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

Sanjay_Bakshi
Starting Member

14 Posts

Posted - 2008-07-21 : 11:38:31

I have one table in which column contain below part,When i am running order by this column it is reflectin E1,E10,E11 i want this to be show like E1,E2,E3,E4...
E1
E2
E3
E4
E5
E6
E7
E8
E9
E10
E11

Sanjay

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 11:39:56

ORDER BY LEN(Col1), Col1

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 11:40:54

[code]SELECT fields
FROM Table
ORDER BY REPLACE(yourEcolumn,'E','')[/code]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-21 : 13:24:50

quote:
Originally posted by visakh16

SELECT fields
FROM Table
ORDER BY REPLACE(yourEcolumn,'E','')

Wouldn't you need to additionally cast this to int for it to work as required?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-21 : 13:44:02

Here's my stab at it:

DECLARE @Yak TABLE (Val VARCHAR(30))

INSERT @Yak
SELECT 'E1,E2,E3,E4...'
UNION ALL SELECT 'E1'
UNION ALL SELECT 'E2'
UNION ALL SELECT 'E3'
UNION ALL SELECT 'E4'
UNION ALL SELECT 'E5'
UNION ALL SELECT 'E6'
UNION ALL SELECT 'E7'
UNION ALL SELECT 'E8'
UNION ALL SELECT 'E9'
UNION ALL SELECT 'E10'
UNION ALL SELECT 'E11'

SELECT 
	*
FROM 
	@Yak
ORDER BY 
	CASE WHEN Val LIKE '%,%' THEN 1 ELSE 0 END DESC, 
	LEN(VAL), 
	Val

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-22 : 03:58:02

quote:
Originally posted by RyanRandall

quote:
Originally posted by visakh16

SELECT fields
FROM Table
ORDER BY REPLACE(yourEcolumn,'E','')

Wouldn't you need to additionally cast this to int for it to work as required?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Yes it is. It should be

ORDER BY REPLACE(yourEcolumn,'E','')+0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources