Unanswered: Sorting SQL output when using "Group By"
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.
SELECT TO_CHAR (Ship_By_Date, 'DD-MON-YYYY') Ship_By_Date,
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;
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.
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?