Order By issue - 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 issue

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-08-14 : 01:08:17

Hi, I have the following data in my table called sheetmonth.

SheetMonth
Aug 2010
Aug 2011
Sept 2010

How can I write my select statement so that it will order by the latest month? Because if the select statement is "select * from sheetmonth order by sheetmonth", it will not order by the latest month which Aug 2011 should at top.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 01:16:20

you need to use a calendar table and join with it to get month number and order by it.
if you had represent month names as per convention then it was just a matter of converting to datetime and ordering. But you've Sept instead of Sep which will error if you're trying to convert to datetime.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-08-14 : 02:56:38

How if I change Sept to Sep? Is it then can be sort using order by without any calendar table?

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-14 : 03:05:13

[code]
update table_name set SheetMonth=sep 2010
where SheetMonth= sept 2010
[/code]
this can change

With Regards
Kashyap M

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-14 : 03:07:54

you can also determine another column where you assign each month a sequal number (e.g.: sep -> 9, may -> 5, etc.) and you order it by this column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 04:36:29

quote:
Originally posted by kashyap_sql

update table_name set SheetMonth=sep 2010
where SheetMonth= sept 2010

this can change

With Regards
Kashyap M

what if OP has other months also in diff format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-08-15 : 00:36:54

Hello, if I change to the following data, it also cannot be sorted.

SheetMonth
8 2010
9 2010
8 2011
9 2011

The data i get using order by is as the following:
8 2010
8 2011
9 2010
9 2011

How can I sort to get the following data:
8 2010
9 2010
8 2011
9 2011

Anyone can guide?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-15 : 02:48:25

sort it firstly by year and then by month.

e.g.:

order by year, month

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-15 : 03:06:04

make order by as

ORDER BY Year,Month

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources