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

SQL Server 2000 Forums

Transact-SQL (2000)

order by

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-06-29 : 05:31:01

There is a field called field1 in a table called table1
Field1 is varchar(50)
Data inside field1 is such as:

3T
apr 2007
feb 2001
jan 2001
dec 1999
...

I am writing a sql query to return the data so that the field1 is sorted:
ie:

3T
dec 1999
jan 2001
feb 2001
apr 2007
...

This is what I have in the order by clause:

cast('01-' + replace(field1, ' ', '-') as datetime)

The problem is if there is something else such as '3T' or another text inside this field1.
Should there be a case statement inside the oprder by clause?

Please note that if there is any text which does not seem to be a date then it should appear first in the list as shown in the example above.
Thanks

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-29 : 05:48:00

Yes, you need a case statement. The statement depends on what kind of string there can be.

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-06-29 : 05:50:27

I don't want to hard code the strings.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-29 : 06:39:42

declare @t table(col1 varchar(50))
insert @t
select 'aez' union all
select '3T' union all
select 'zzz' union all
select 'dec 1999' union all
select 'jan 2007' union all
select 'feb 2001' union all
select 'apr 2005'

Select * from @t
order by case when isdate('01-' + replace(col1, ' ', '-')) = 1 then
cast('01-' + replace(col1, ' ', '-') as datetime)
Else
cast('01/01/1900' as datetime)
End

--------------------------------------------------
S.Ahamed

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-29 : 06:41:21

You don't have to.
For example is they are all shorter than 8 characters

ORDER BY
CASE WHEN LEN(field1) < 8 THEN 1 ELSE 2 END,
CASE WHEN LEN(field1) < 8 THEN field1 ELSE cast('01-' + replace(field1, ' ', '-') as datetime) END

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-29 : 06:45:26

Ah, that is a nice and clear solution.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 09:06:37

1 You dont follow your threads http://sqlteam.com/forums/topic.asp?TOPIC_ID=85574
2 As I told you, always use proper datatype DATETIME to store dates

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 09:09:54

quote:
Originally posted by pbguy

declare @t table(col1 varchar(50))
insert @t
select 'aez' union all
select '3T' union all
select 'zzz' union all
select 'dec 1999' union all
select 'jan 2007' union all
select 'feb 2001' union all
select 'apr 2005'

Select * from @t
order by case when isdate('01-' + replace(col1, ' ', '-')) = 1 then
cast('01-' + replace(col1, ' ', '-') as datetime)
Else
cast('01/01/1900' as datetime)
End

--------------------------------------------------
S.Ahamed

Note that like ISNUMERIC, ISDATE also not reliable

Select ISDATE('2006'),ISDATE(2006)

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-29 : 09:52:22

You have 429 posts here and you still have not learned to use correct datatypes!?? ALWAYS use proper datatypes for your data and this will NEVER be an issue. Why do people insist of making things harder on themselves ?????

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 23:30:08

quote:
Originally posted by jsmith8858

You have 429 posts here and you still have not learned to use correct datatypes!?? ALWAYS use proper datatypes for your data and this will NEVER be an issue. Why do people insist of making things harder on themselves ?????

- Jeff
http://weblogs.sqlteam.com/JeffS

Becuase they want to write VERY complicated queries

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources