985680 wrote:Right. In the outer query, you're GROUPing BY
I have grouped the log count for every one hour for the past one month. No I wanted to find the minimum (Hour with minimum logs) for each day. Could you please help me out?
The below query doesn't work:
select to_char(HOURLY, 'MM/DD/YYYY HH24:MI:SS'), min(sms) from
(select trunc(createdate,'hh24') + (trunc(to_char(createdate,'mi')/60)*60)/24/60 HOURLY, count(*) sms
where createdate >= to_date('01/01/2013', 'MM/DD/YYYY')
group by trunc(createdate,'hh24') + (trunc(to_char(createdate,'mi')/60)*60)/24/60)
group by to_char(Hourly, 'MM/DD')
I get ORA: 00979 error. Could some one help me out?
but in the SELECT clause , you're trying to reference
which is not the same as the GROUP BY expression, and does not depend on the GROUP BY expression.
to_char(HOURLY, 'MM/DD/YYYY HH24:MI:SS')
WITH hour_summary AS ( SELECT TRUNC (createdate, 'HH') AS hour , COUNT (*) AS hour_cnt , RANK () OVER ( PARTITION BY TRUNC (TRUNC (createdate, 'HH')) ORDER BY COUNT (*) ) AS day_rank FROM testlog WHERE createdate >= TO_DATE ( '01/01/2013' , 'MM/DD/YYYY' ) GROUP BY TRUNC (createdate, 'HH') ) SELECT hour , hour_cnt FROM hour_summary WHERE day_rank = 1 ORDER BY hour ;
doubtsinora wrote:Yes, there are several ways to do it without using RANK or any similar function, depending on your data and your requirements.
... I was wondering if this can be done without a rank function?