Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Posts
    10

    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.

    Hope this makes sense and I appreciate any help.

    Thanks,
    Jaz

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Apr 2004
    Posts
    10

    Sorted By Date

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i gotta see this, 'cause right now i don't believe it

    can you show your query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Apr 2004
    Posts
    10
    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;

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2004
    Posts
    10
    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.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the column alias

    COLUMN PRINTShipDate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68

    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;

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by r937
    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?

  13. #13
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •