Link to home
Start Free TrialLog in
Avatar of jstorm
jstorm

asked on

SQL Query Question Ordering By Date

I have a query that I need some help with.  Below is the query.
I am interested in getting it to show the information but ordered
by oldest date first.

--======================================================================
select min(convert(varchar(10),rh.dateentered,101)) as [DateCreated],
       item01, item02, item03, item04, sum(item05) as TotalOrders

from table01

group by item01, item02, item03, item04

order by min(convert(varchar(10),rh.dateentered,101))
--======================================================================

I need to use the group by because I am consolidating item01.

I need the other information to be shown on the query and that is why
I do not use distinct.

My problem is that I get this query ordered by the first part of the
date.  The dateentered is a datetime, and I have converted it to a
short date.

01/01/2007  ......
02/01/2007  ......
06/01/2007  ......
11/01/2006  ......

This is not really ordering by oldest date as I want.  Can anyone help
me get this lined out?  The data should read...

11/01/2006  ......
01/01/2007  ......
02/01/2007  ......
06/01/2007  ......

Thanks for the help.

ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
select convert(varchar, min(rh.dateentered), 101) as [DateCreated],
       item01, item02, item03, item04, sum(item05) as TotalOrders

from table01

group by item01, item02, item03, item04

order by convert(varchar, min(rh.dateentered), 101)
Avatar of jstorm
jstorm

ASKER

matthewspatrick:

This changed it but now output is

12/01/06
11/01/06
..
10/01/07
09/01/06
01/01/07

so it still does not order from the oldest to newest.
That is because his query still returns a varchar in the ordering...no change at all.  Did you try my query?  It sorts by date.
Avatar of jstorm

ASKER

I hope I did everything right on award points... still sorta new.

Thank you both so much for the help, you guys are great...

:)
jstorm said:
>>I hope I did everything right on award points... still sorta new.

You did.  chapmandrew had it right, and I did not :)