Home » SQL & PL/SQL » SQL & PL/SQL » Returning first rank after aggregating
Returning first rank after aggregating [message #580724] Wed, 27 March 2013 09:10 Go to next message
urbanmojo
Messages: 9
Registered: March 2013
Junior Member
Hi:

I need to return which hour for a given date range had the most calls. I have a query that works but it is inelegant and I'm pretty sure I could do better. I'm pretty new to analytic queries so go easy...Smile

select hour,
       calls 
from
(
select hour,
       calls, 
       rank() over (ORDER BY calls desc) as ranking
from
(
select 
	TRUNC(calltimestamp_local, 'HH24') as hour,
	count(*) as calls
	from
	call_record c
		
	where 
            c.calltimestamp_local  >= to_date('1-FEB-2013','dd-MON-yyyy')
	AND 
            c.calltimestamp_local <  to_date('18-FEB-2013', 'dd-MON-yyyy')
	and
            c.destinationnumber =  '555-555-1212'
	group by
		
	TRUNC(calltimestamp_local, 'HH24')
)
)
where ranking = 1


Any help?

Thanks!
Re: Returning first rank after aggregating [message #580726 is a reply to message #580724] Wed, 27 March 2013 09:33 Go to previous message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Sat, 23 March 2013 20:07
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Previous Topic: product and category query, please help
Next Topic: Use index to minimize the lock on parent table
Goto Forum:
  


Current Time: Sun Apr 20 17:12:59 CDT 2014

Total time taken to generate the page: 0.26152 seconds