Order By is not working - 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 is not working

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-10-06 : 20:42:40

select hire_date, cnt from
(select convert(char(10), hire_date, 105) hire_date, count(hire_date) cnt
from employee
group by hire_date) a
order by hire_date

Run this query using the pubs database and see what happens. This does not sorts the records.

ikhuram
Starting Member

28 Posts

Posted - 2005-10-06 : 20:59:57

Try this

select hire_date, cnt from
(select convert(char(10), hire_date, 105) hire_date, count(hire_date) cnt
from employee
group by hire_date) a
order by convert(smalldatetime, hire_date, 105)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-06 : 21:02:25

it is ordering by the converted value (which is no longer a date).
try this:


select  convert(char(10), hire_date, 105) hire_dateStr, cnt
from	(select hire_date, count(*) cnt 
	from employee 
	group by hire_date) a
order by hire_date

EDIT:
I guess mine only works because there is only 1 row per hire date day anyway. But you get the idea.

You should try to avoid doing conversion from and to dates if you can help it. Use the front end app to format the date.

Here is an alternative to group by days without the conversions. Return this and format it as desired in the presentation layer


select	dateadd(day, datediff(day,0,hire_date),0), count(*) cnt
from	employee
group by dateadd(day, datediff(day,0,hire_date),0)
order by 1

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-06 : 22:08:15

Bridge -- what is the datatype of the expression you are sorting by?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-07 : 01:06:33

If you want to show this in Presentation layer, then run this and do the Date conversion there

select hire_date, count(hire_date) cnt
from employee
group by hire_date

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-07 : 07:29:48

quote:
Originally posted by madhivanan

If you want to show this in Presentation layer, then run this and do the Date conversion there

select hire_date, count(hire_date) cnt
from employee
group by hire_date

the only problem with this is if he has multiple datetimes within each day. I think Bridge wants one row per day.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-07 : 07:33:09

>>I think Bridge wants one row per day.

I dont think so

>>Run this query using the pubs database and see what happens. This does not sorts the records.

I think he is worried about the ordering only

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-07 : 07:37:10

Maybe you're right, I guess we'll never know since he seems to have got what he needed and bugged out :)

since he was grouping by the converted date format (dd-mm-yyyy) I assumed he wanted one row per day. Who knows...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources