January 24, 2013 at 9:44 am
I have a table with the data below. As you can see it is in the wrong order.
2011December2919.72995963
2011November99.70399538
2012April 20097.44533445
2012August32469.21863669
2012December51689.1334895092
2012February12366.6582281
2012January8574.49626132003
2012July 23184.69614219
2012June 23111.50947556
2012March16066.8675222
2012May 22635.03714318
2012November48820.5590190995
2012October44376.7277266496
2012September40879.2832671298
2013January56680.6602927389
__________________________________________
I want to order it like so:
2011November99.70399538
2011December2919.72995963
2012January8574.49626132003
2012February12366.6582281
2012March16066.8675222
2012April 20097.44533445
2012May 22635.03714318
2012June 23111.50947556
2012July 23184.69614219
2012August32469.21863669
2012September40879.2832671298
2012October44376.7277266496
2012November48820.5590190995
2012December51689.1334895092
2013January56680.6602927389
Re-creating the table with an identity seed would fix it but I can't do that as I would have to enter all the data to the table again. Is there a way around it?
Thanks for
January 24, 2013 at 9:49 am
Whenever you want to see records in tables selected in the specific order, you should apply ORDER BY.
Actually, you cannot enforce the order of how data is stored physically on a disk. Even applying clustering index only really ensures logical order...
Even if your table would be clustered in the way you want, you still need to use ORDER BY to guarantee
an order when you select from it!
January 24, 2013 at 10:04 am
how about this
declare @t table(yr int,mon varchar(32))
Insert into @t
select 2011,' December ' union
select 2012,' July ' union
select 2012,' June ' union
select 2012,' March ' union
select 2012,' May ' union
select 2012,' November 'union
select 2012,' October ' union
select 2012,' September' union
select 2011,' November ' union
select 2012,' April ' union
select 2012,' August ' union
select 2012,' December' union
select 2012,' February ' union
select 2012,' January '
select * from @t order by yr,DATEPART(mm,cast(mon+' 1, 1900' as date))
January 24, 2013 at 10:15 am
Actually it's OK. The records go into the table in the proper order so when I do a simple select without an "order by" clause, they come back in the right order (SELECT YEAR,MONTH,savings FROM TBL_savings).
If I put in the "order by" clause (SELECT YEAR,MONTH,savings FROM TBL_savings order by YEAR,MONTH) then I get them in the wrong order as it orders the months alphabetically (April comes first, September last;see what months are in 2012 in my first post).
Leaving out the order by clause fixed it strangely.
When months are in a table and they are called by their name (January, February....) an order by clause brings them back in alphabetical order. When months are in a table and they are called by number (1 for January, 2 for February etc.) an order by clause brings them back in numerical order.
A cast would fix it as the last poster said.
January 24, 2013 at 3:53 pm
leesider (1/24/2013)
Actually it's OK. The records go into the table in the proper order so when I do a simple select without an "order by" clause, they come back in the right order (SELECT YEAR,MONTH,savings FROM TBL_savings).If I put in the "order by" clause (SELECT YEAR,MONTH,savings FROM TBL_savings order by YEAR,MONTH) then I get them in the wrong order as it orders the months alphabetically (April comes first, September last;see what months are in 2012 in my first post).
Leaving out the order by clause fixed it strangely.
When months are in a table and they are called by their name (January, February....) an order by clause brings them back in alphabetical order. When months are in a table and they are called by number (1 for January, 2 for February etc.) an order by clause brings them back in numerical order.
A cast would fix it as the last poster said.
The problem with your "when I do a simple select without an "order by" clause, they come back in the right order" is the fact that in some moment it may stop to return records in the order you can see now....
The problem with your "If I put in the "order by" clause (SELECT YEAR,MONTH,savings FROM TBL_savings order by YEAR,MONTH)" is the fact that your expectation are wrong. String month names are just strings for SQL Server. Therefore your ORDER BY clause is wrong!
Try to use the following:
...
ORDER BY CAST('1 ' + MONTH + ' ' + CAST(YEAR AS VARCHAR) AS DATE)
The above will guarantee the order you want.
January 25, 2013 at 1:21 am
Eugene Elutin (1/24/2013)
leesider (1/24/2013)
Actually it's OK. The records go into the table in the proper order so when I do a simple select without an "order by" clause, they come back in the right order (SELECT YEAR,MONTH,savings FROM TBL_savings).If I put in the "order by" clause (SELECT YEAR,MONTH,savings FROM TBL_savings order by YEAR,MONTH) then I get them in the wrong order as it orders the months alphabetically (April comes first, September last;see what months are in 2012 in my first post).
Leaving out the order by clause fixed it strangely.
When months are in a table and they are called by their name (January, February....) an order by clause brings them back in alphabetical order. When months are in a table and they are called by number (1 for January, 2 for February etc.) an order by clause brings them back in numerical order.
A cast would fix it as the last poster said.
The problem with your "when I do a simple select without an "order by" clause, they come back in the right order" is the fact that in some moment it may stop to return records in the order you can see now....
The problem with your "If I put in the "order by" clause (SELECT YEAR,MONTH,savings FROM TBL_savings order by YEAR,MONTH)" is the fact that your expectation are wrong. String month names are just strings for SQL Server. Therefore your ORDER BY clause is wrong!
Try to use the following:
...
ORDER BY CAST('1 ' + MONTH + ' ' + CAST(YEAR AS VARCHAR) AS DATE)
The above will guarantee the order you want.
So does this:
ORDER BY CAST(mon + CAST(yr AS VARCHAR) AS DATETIME)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply