Hi, I've used the "group by" function to extract data from my Oracle database, but am having problems when trying to sort the output.

I want to sort the data based on date (which is the 1st output column), but when I try and do this the output is sorted within each month and each months data is sorted independently of the other months' data.

e.g. if 8 rows are pulled out for April then these are sorted in ascending order. Then if 8 rows are pulled out for May, these are sorted in ascending order independently of the April data or any other months data pulled out.

I can't find a way to pull out the data using the "group by" function, but then sort the whole output on the date column.

Hope this makes sense and I appreciate any help.

Thanks,
Jaz

Please sort these example dates the way you'd like to see them:

2004-01-01
2004-01-02
2004-01-03
2004-01-15
2004-02-04
2004-02-05
2004-02-06
2004-02-15
2004-03-15
2004-03-27
2004-03-28
2004-03-29

-PatP

Hi Pat, I would like the dates sorted in the order you have listed them: from oldest to most recent.

The way it is coming out at the moment (using your data) is as follows:

if I use "Sort By Field_Name DESC" it comes out as:
2004-01-15
2004-01-03
2004-01-02
2004-01-01
2004-02-15
2004-02-06
2004-02-05
2004-02-04
2004-03-29
2004-03-28
2004-03-27
2004-03-15

If I use "Sort By Field_Name ASC" it comes out as:

2004-03-15
2004-03-27
2004-03-28
2004-03-29
2004-02-04
2004-02-05
2004-02-06
2004-02-15
2004-01-01
2004-01-02
2004-01-03
2004-01-15


Thanks,
Jaz

i gotta see this, 'cause right now i don't believe it

can you show your query?

I'm with Rudy on this one. I've never seen anything like that, unless maybe you are sorting the dates in some kind of alphanumeric form instead of sorting them as dates.

-PatP

I know it is strange ! Here's the code I'm using:

SELECT TO_CHAR (Ship_By_Date, 'DD-MON-YYYY') Ship_By_Date,
ORDER_HEADER.Order_ID,
ORDER_HEADER.Name,
ORDER_HEADER.Town,
ORDER_HEADER.Status,
MOVE_TASK.Work_Zone,
COUNT(*)
FROM ORDER_HEADER,
MOVE_TASK
WHERE ORDER_HEADER.Client_ID = '3MHC' AND
ORDER_HEADER.From_Site_ID = '&1' AND
ORDER_HEADER.Work_Group LIKE '90%' AND
((ORDER_HEADER.Status = 'Released') OR
(ORDER_HEADER.Status = 'Allocated') OR
(ORDER_HEADER.Status = 'Short')) AND
MOVE_TASK.TASK_ID = ORDER_HEADER.ORDER_ID
group by MOVE_TASK.WORK_ZONE, ORDER_HEADER.ORDER_ID, ORDER_HEADER.STATUS, ORDER_HEADER.NAME, ORDER_HEADER.TOWN, Ship_By_Date
ORDER BY Ship_By_Date ASC, ORDER_HEADER.Order_ID;

well of course there's your answer

you are sorting by DD-MON-YYYY

do this:

SELECT TO_CHAR(Ship_By_Date, 'DD-MON-YYYY') as PRINTShipDate
, TO_CHAR(Ship_By_Date, 'YYYYMMDD') as SORTShipDate
...
group by ... , PRINTShipDate, SORTShipDate
ORDER BY SORTShipDate ...

Thanks rudy, I see why I was having problems now.

But I still can't see how to get the PRINT version of the date to show while still pulling out the SORT version as if I call the output anything other than "Ship_By_Date" it comes up as invalid identifier.

I'm outputting the data to a report as follows:

COLUMN Ship_By_Date HEADING 'Ship Date' FORMAT A11
COLUMN Order_ID HEADING 'Order ID' FORMAT A15 WORD_WRAP
COLUMN Name HEADING 'Name' FORMAT A30 WORD_WRAP
COLUMN Town HEADING 'Location' FORMAT A30 WORD_WRAP
COLUMN Status HEADING 'Status' FORMAT A15 WORD_WRAP
COLUMN Work_Zone HEADING 'Work Zone' FORMAT A10 WORD_WRAP
COLUMN COUNT(*) HEADING 'No. Of Tasks' FORMAT 9999

Apologies for the queries, but I'm quite new to SQL.

Thanks for your help.

use the column alias

COLUMN PRINTShipDate

Forget the hocky pocky.

Take your original query and convert your Ship_By_Date back to date in the order by clause.

ORDER BY to_date(Ship_By_Date) ASC, ORDER_HEADER.Order_ID;

nocopy, sorting on TO_CHAR(Ship_By_Date, 'YYYYMMDD'), as in the query i suggested, will produce the correct sequence, although i suppose i could have just left it alone

so, um, why do you need to use TO_DATE when Ship_By_Date is already a date????

Because the db picks up a select list alias (date converted to character) in the order by clause, thus an undesirable sort order.
Is this not so?
Converting it back to date is the shortest fix. Creating an additional column is "hocky pocky".
Do you disagree?

Now, that is assuming you are stuck on aliasing a column the same name and the db field. If you'd be willing to change even one character then there would have been no problem in the first place.

I like that. A one character solution.