whats wrong in the Order By Clause

Question

whats wrong in the Order By Clause

isa-369599

SSCommitted

Points: 1602
More actions
April 2, 2008 at 11:47 am

#184675

Hello everyone, I m suing SQL Server 2005, here is my query, which displays the last 7 days data in this i used the order by clause for sorting my data . My query returns the following dates in this manner:
LastUpdate Count
4/1/2005 5
3/28/2008 4
3/29/2008 2
3/31/2008 10
Here is my Query:
SELECT Convert(Char(15),LastUpdate,106) as LastUpdate,COUNT(TvsRecordID)as Count
FROM TvsRecords
where LastUpdate between DATEADD(dd, -7,getdate())
and getdate()
andTvsFormStatusCode =@TvsFormStatusCode
GROUP BY Convert(Char(15),LastUpdate,106)
ORDER BY LastUpdate desc
in the result , it display April data first but other dates of previous month shows properly, kindly tell me whats wrong with this query and y not 4/1/2008 shows after the 3/31/2008 .Kindly reply me and Thanx in Advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Beggs SSCommitted Points: 1921 More actions · Answer 1

Sorry, but I am confused. Your results don't match your query. Format 106 is "dd mon yyyy". Also, the order you mention as being correct does not match the data. And the entry for April is from 2005.

Chances are that you are having an order issue because of the conversion of the date to a char, but it would be nice to see the true results of your query.

GSquared SSC Guru Points: 260824 More actions · Answer 2

Not sure what you're doing here. I tried this:

create table #TvsRecords (

LastUpdate datetime,

TvsRecordID int identity primary key)

insert into #TvsRecords (lastupdate)

select '4/1/2005' union all

select '3/28/2008' union all

select '3/29/2008' union all

select '3/31/2008' union all

select '3/31/2008'

SELECT Convert(Char(15),LastUpdate,106) as LastUpdate,COUNT(TvsRecordID)as Count

FROM #TvsRecords

where LastUpdate between DATEADD(dd, -7,getdate())

and getdate()

--and TvsFormStatusCode =@TvsFormStatusCode

GROUP BY Convert(Char(15),LastUpdate,106)

ORDER BY LastUpdate desc

And got:

LastUpdateCount

31 Mar 2008 10

29 Mar 2008 2

28 Mar 2008 4

Something's not right in what you presented. Not sure what. Take another look at your code and output, please.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

I believe it's confusion regarding the data column in the table, versus the column alias in the query:

SELECT Convert(Char(15),LastUpdate,106) as LastUpdate..

...

ORDER BY LastUpdate desc

The 2 "LastUpdate" references above are not the same thing. You're ordering by the non-converted tablec olumn. You can't reference an alias in the ORDER BY.

Try it this:

SELECT Convert(Char(15),LastUpdate,106) as Foo..

...

ORDER BY Foo desc

Error, right ?

John Beggs SSCommitted Points: 1921 More actions · Answer 4

@PW

Actually, the ORDER BY can apply to either the alias or the real column name. Now, with the example above, you will have to work around the GROUP BY restrictions, but in a non-aggregating select statement you can use either. And in the case of a DATETIME vs a CHAR, the order will be different.

Regardless, the original post does not make sense as the results that are given do not match the query, in several ways. The OP still needs to provide us with some real examples to get a specific answer.

GSquared SSC Guru Points: 260824 More actions · Answer 5

PW (4/2/2008)
I believe it's confusion regarding the data column in the table, versus the column alias in the query:
SELECT Convert(Char(15),LastUpdate,106) as LastUpdate..
...
ORDER BY LastUpdate desc
The 2 "LastUpdate" references above are not the same thing. You're ordering by the non-converted tablec olumn. You can't reference an alias in the ORDER BY.
Try it this:
SELECT Convert(Char(15),LastUpdate,106) as Foo..
...
ORDER BY Foo desc
Error, right ?

In SQL 2000, you'd be right. Since this is the 2005 forum, and in 2005 you can Order By/Group By/Having a column alias, I'm assuming that's not the issue here.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

kindly tell me whats wrong with this query and y not 4/1/2008 shows after the 3/31/2008

Is it because 4/1/2008 is not in your result set? Or is this a typo?

Also, if you look at your query you will notice that your results are in Ascending order, while in the query you posted you specifiy descending.

ORDER BY LastUpdate desc

LastUpdate Count
4/1/2005 5
3/28/2008 4
3/29/2008 2
3/31/2008 10

How can your output look like this is you are converting using "Convert(Char(15),LastUpdate,106) "?

My blog: http://jahaines.blogspot.com