| Help: ORDER BY with GROUP BY [message #371530] |
Thu, 02 November 2000 15:19  |
Miguel Albrecht
Messages: 1
Registered: November 2000
|
Junior Member |
|
|
I have a short report that shows hits (counts)
grouped by day. The select looks like this:
select to_char(dayt, 'YYYY Mon DD, Dy'), count(*)
from table group by to_char(dayt, 'YYYY Mon DD, Dy')
So far so good. Until Nov 1st, the list was well
ordered, e.g. Oct 26 _after_ Oct 25. On Nov 1st
the list shows Nov 1st _before_ Oct 31 -- Obvious,
'N' is alphabetically lower than 'O'.
So I tried to add an order by clause to the select
such as 'order by dayt' (this works on SQL Server)
alas not with oracle: it complains about dayt
not being in the group by columns.
Help! Any Ideas??
|
|
|
|
| Re: Help: ORDER BY with GROUP BY - try this [message #371531 is a reply to message #371530] |
Thu, 02 November 2000 15:38  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
It's true that you must order by something in your GROUP BY clause, but not all GROUP BY columns need to be in the resultset. Add a numerically descending date col. like YYYMMDD and then order on that. Note that the YYYMMDD is equivalent to your selected column in value, but not format.
SELECT TO_CHAR(dayt, 'YYYY Mon DD, Dy'), COUNT(*)
FROM TABLE
GROUP BY TO_CHAR(dayt, 'YYYY Mon DD, Dy'), TO_CHAR(dayt, 'YYYYMMDD')
ORDER BY TO_CHAR(dayt, 'YYYYMMDD')
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
