can't seem to get my GROUP BY ordering right

I have the following query, which works, but the ordering isn't quite
right, can anyone help?
SELECT
to_char(date_created,'DD-MON-YYYY') as date_joined,
COUNT(user_id) AS user_count
FROM
project_users
GROUP BY
to_char(date_created,'DD-MON-YYYY')
ORDER BY
to_char(date_created,'DD-MON-YYYY')
the output is shwon as follows:
02-APR-2006 - 1
03-APR-2006 - 1
04-APR-2006 - 4
05-APR-2006 - 8
06-APR-2006 - 11
08-MAR-2006 - 2
16-MAR-2006 - 2
17-MAR-2006 - 2
20-MAR-2006 - 3
21-MAR-2006 - 1
22-MAR-2006 - 1
but I would rather it was more logically ordered for a report, as so:
06-APR-2006 - 11
05-APR-2006 - 8
04-APR-2006 - 4
03-APR-2006 - 1
02-APR-2006 - 1
22-MAR-2006 - 1
21-MAR-2006 - 1
20-MAR-2006 - 3
17-MAR-2006 - 2
16-MAR-2006 - 2
08-MAR-2006 - 2
i.e. reverse order of date, and calendar month (not by month starting
letter)
TIA for any help and advicedid you try using DESC (for descending)?|||Kevin,
try
ORDER BY date_created DESC|||Also I suspect you're going to have to order desc by year/month/day
instead of d/m/y|||whooops.. forgot the mention that bit.. yup.. if I add DESC then the
order is reversed by DD, then MON, then YYYY... but that means that the
MAR dates appear before APR, as alphabetically, and in reverse order, M
comes before A.
I guess I should have mentioned that bit, eh' thanks for the quick
replies btw|||What if you try this (let me know if it helps..)
SELECT
to_char(date_created,'DD-MON-YYYY') as date_joined,
COUNT(user_id) AS user_count
FROM
project_users
GROUP BY
to_char(date_created,'DD-MON-YYYY')
ORDER BY
date_created desc
"Kevin Blount" wrote:

> I have the following query, which works, but the ordering isn't quite
> right, can anyone help?
> SELECT
> to_char(date_created,'DD-MON-YYYY') as date_joined,
> COUNT(user_id) AS user_count
> FROM
> project_users
> GROUP BY
> to_char(date_created,'DD-MON-YYYY')
> ORDER BY
> to_char(date_created,'DD-MON-YYYY')
>
> the output is shwon as follows:
> 02-APR-2006 - 1
> 03-APR-2006 - 1
> 04-APR-2006 - 4
> 05-APR-2006 - 8
> 06-APR-2006 - 11
> 08-MAR-2006 - 2
> 16-MAR-2006 - 2
> 17-MAR-2006 - 2
> 20-MAR-2006 - 3
> 21-MAR-2006 - 1
> 22-MAR-2006 - 1
> but I would rather it was more logically ordered for a report, as so:
> 06-APR-2006 - 11
> 05-APR-2006 - 8
> 04-APR-2006 - 4
> 03-APR-2006 - 1
> 02-APR-2006 - 1
> 22-MAR-2006 - 1
> 21-MAR-2006 - 1
> 20-MAR-2006 - 3
> 17-MAR-2006 - 2
> 16-MAR-2006 - 2
> 08-MAR-2006 - 2
> i.e. reverse order of date, and calendar month (not by month starting
> letter)
> TIA for any help and advice
>|||"Kevin Blount" <kevin.blount@.gmail.com> wrote in message
news:1144353616.181845.72600@.e56g2000cwe.googlegroups.com...
> whooops.. forgot the mention that bit.. yup.. if I add DESC then the
> order is reversed by DD, then MON, then YYYY... but that means that the
> MAR dates appear before APR, as alphabetically, and in reverse order, M
> comes before A.
> I guess I should have mentioned that bit, eh' thanks for the quick
> replies btw
>
I think you should have mentioned that this isn't a SQL Server query at all.
Oracle?
Anyway, try:
ORDER BY MIN(date_created)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David, using ' MIN(date_created) DESC ' worked a charm (just '
date_created DESC ' didn't work at all for some reason).
I must confess, I saw SQL in the group name, but missed the SQLSERVER
part.. but then it's tricky finding a general group for queries..
unless someone knows of one?|||"Kevin Blount" <kevin.blount@.gmail.com> wrote in message
news:1144354904.773319.110340@.i40g2000cwc.googlegroups.com...
> I must confess, I saw SQL in the group name, but missed the SQLSERVER
> part.. but then it's tricky finding a general group for queries..
> unless someone knows of one?
There are specific groups for Oracle:
comp.databases.oracle.server
comp.databases.oracle.misc
For others check out http://groups.google.com
Whatever the product you are using, *always* state your version number.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--