|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
can't seem to get my GROUP BY ordering rightright, 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 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 "Kevin Blount" <kevin.blo***@gmail.com> wrote in message I think you should have mentioned that this isn't a SQL Server query at all. 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 > 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.blo***@gmail.com> wrote in message There are specific groups for Oracle: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? 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 -- 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 Show quote "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 > >  |
|||||||||||||||||||||||
Other interesting topics