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
Messages: 9
Registered: March 2013
Junior Member

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

Any help?

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: 65143
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.


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

Current Time: Fri Aug 18 19:05:30 CDT 2017

Total time taken to generate the page: 0.14872 seconds