Re: QUERY (top 10)

  • From: rjamya <rjamya@xxxxxxxxx>
  • To: oracledbam@xxxxxxxxxxx
  • Date: Mon, 16 May 2005 09:02:04 -0400

Seema,

your output doesn't make sense ... if you want top 10 by domain, why
yahoo.com is listed multiple times? If you wat by domain try something
like

select *
from (select trunc(date_col), domain, count(spam_msgs),=20
                  rank () over (order by count(spam_msgs) partition by
 trunc(date_col) r)
           from my_table group by trunc(date_col), domain)
where r <=3D 10
/

of course check for syntax ...
Raj

On 5/16/05, Seema Singh <oracledbam@xxxxxxxxxxx> wrote:
> hi,
>=20
> I want report datewise  top 10 spam sender domain.
> Date,domain and spam(interger ) are columns of table.This query is not
> working .
> the output should be like
>=20
> date                 domain                  spam
> 05/05/05         hotmail.com            120
> 05/05/05         yahoo.com              110
> 05/05/05         yahoo.com              99
> 05/05/05         yahoo.com              80
> 05/05/05         yahoo.com              79
> --------------------------------------------------
> 05/06/05         yahoo.com              300
> 05/06/05         yahoo.com              250
> 05/06/05         yahoo.com              200
>=20
> thanks
>=20
> >From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
> >To: <oracledbam@xxxxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx>
> >Subject: Re: QUERY (top 10) Date: Sun, 15 May 2005 22:05:29 +0200
> >
> >Seema,
> >
> >
> >
> >>I want report datewise  top 10 spam sender domain.
> >
> >
> >I assume the basic select to get the count of spam per day is something
> >like that (correct if inappropriate)
> >
> >
> >
> >select trunc(time1) send_date, report_sender_domain_id, count(*) cnt
> >
> >from spam
> >
> >where spam is not null
> >
> >group by trunc(time1), report_sender_domain_id
> >
> >
> >
> >to get top 10 you simple use row_number() function and limit the row num=
ber
> >to 10:
> >
> >
> >
> >select * from (
> >
> >  select send_date, report_sender_domain_id, cnt,
> >
> >  row_number() over (partition by send_date order by cnt desc) as rn
> >
> >  from ( -- your basic select here
> >
> >    select trunc(time1) send_date, report_sender_domain_id, count(*) cnt
> >
> >    from spam
> >
> >    where spam is not null
> >
> >    group by trunc(time1), report_sender_domain_id
> >
> >  )
> >
> >)
> >
> >where rn <=3D 10
> >
> >order by send_date,  rn;
> >
> >
> >
> >You may also consider the function CUME_DIST if you want top 10%
> >
> >
> >
> >HTH
> >
> >
> >
> >Jaromir
> >
> >----- Original Message ----- From: "Seema Singh" <oracledbam@xxxxxxxxxxx=
>
> >To: <oracle-l@xxxxxxxxxxxxx>
> >Sent: Sunday, May 15, 2005 8:39 PM
> >Subject: QUERY (top 10)
> >
> >
> >
> >
> >
>=20
> --
> http://www.freelists.org/webpage/oracle-l
>=20


--=20
------------------------------
select standard_disclaimer from company_requirements where category =3D
'MANDATORY';
--
http://www.freelists.org/webpage/oracle-l

Other related posts: