| Top 3 in group by [message #374620] |
Thu, 21 June 2001 13:36  |
Eric
Messages: 78
Registered: April 1999
|
Member |
|
|
HI,
Here my problem:
I Have a table with 2 columns: CREAT_DT and USER_ID.
This table is aimed to log date and user that hits on a specified button.
What I want is to ranks the TOP 3 USER_ID that have made more hits by months. Here the desired result:
MONTH/ USER/ HITS
-------------------------------------
2000 October / user123 / 1254
2000 October / user55 / 1147
2000 October / user698 / 254
2000 November / user547 / 3325
2000 November / user123 / 1458
2000 November / user55 / 325
(...)
Thanks!
|
|
|
|
| Re: Top 3 in group by [message #374621 is a reply to message #374620] |
Thu, 21 June 2001 14:22  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
Some minor tweaking of this stored procedure should get you where you want to be:
create or replace procedure topthree is
--
cursor topusers is
select to_char(create_dt,'YYYY') YEAR, to_char(create_dt,'Month') MONTH, usr_id, to_char(create_dt,'YYYYMM') , count(*) count
from hits group by to_char(create_dt,'YYYY') , to_char(create_dt,'Month'), usr_id, to_char(create_dt,'YYYYMM')
order by 4 desc, 5 desc;
--
month_cntr NUMBER;
month_save VARCHAR2(20) := 'XXXX';
--
BEGIN
--
FOR currec in topusers
LOOP
IF currec.MONTH != month_save THEN
month_cntr := 0;
month_save := currec.MONTH;
END IF;
month_cntr := month_cntr + 1;
IF month_cntr > 3 THEN NULL;
ELSE
dbms_output.put_line(currec.YEAR||' '||currec.MONTH||' '||currec.usr_id||' '||currec.count);
END IF;
END LOOP;
END;
|
|
|
|
| Re: Top 3 in group by [message #374628 is a reply to message #374620] |
Fri, 22 June 2001 00:06 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
Since the Oracle8 or above allows to create an instant virtual table in the select statement itself, it is possible to resolve your problem with a simple select statement. Try out the following SQL select statement and let me know.
SELECT CREAT_DT,USER_ID,TOP_NUMHITS FROM (SELECT CREAT_DT,USER_ID,SUM(NUMHITS) TOP_NUMHITS FROM your_table GROUP BY CREAT_DT,USER_ID) ORDER BY CREATE_DT,USER_ID,TOP_NUMHITS DESC;
First the instant virtual table groups the rows by date and userid and then the outer ORDER BY clause sorts them in descrending order of date,id and number of hits. Hope this helps.
Sudhakar.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
