Sql query...please help its urgent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sql query...please help its urgent

Author

Topic

maven_9999
Starting Member

7 Posts

Posted - 2004-11-21 : 20:58:16

HI ALL,

GOT STRUCK WITH AN PROBLEM, ITS VERY URGENT FOR ME. EXPERTS PLEASE HELP.

I HAVE AN TABLE LIKE BELOW.

TABLE1 :

IP - SERVICE - HIT

1.1.1.1 - UDP - 200
1.1.1.1 - TCP - 300
1.1.1.1 - UDP - 200
2.2.2.2 - UDP - 100
2.2.2.2 - TCP - 50
2.2.2.2 - TCP - 100

TABLE1 COTAINS SOME TYPE OF LOG DATA, WHICH I HAVE TO COPY TO TABLE2.

TABLE2 LOOKS LIKE BELOW.I AM TRYING FOR A (1 OR 2 QUERIES) QUERY WHICH SHOULD COPY DATA FROM TABLE1 TO TABLE2
LIKE BELOW.

TABLE2 :

IP - SERVICE -HIT - RANK1 - RANK2

1.1.1.1 - UDP - 400 - 1 - 1
1.1.1.1 - TCP - 300 - 1 - 2
2.2.2.2 - UDP - 100 - 2 - 2
2.2.2.2 - TCP - 150 - 1 - 1

IP : FROM TABLE1 TO TABLE2 SHOULD BE COPIED LIKE....GROUP BY SERVICE WHERE IP = IP
SERVICE : WILL BE COPIED GROUP BY SERVICE
HIT : SUM OF HIT GROUP BY SERVICE WHERE IP = IP
RANK1 : GROUP BY IP AND SUM OF HIT, MAX HIT IP WOULD BE TOP RANK.
RANK2 : GROUP BY IP,SERVICE AND SUM OF HIT, MAX HIT SERVICE WILL BE TOP RANK WHERE IP = IP.

THANKS IN ADVANCE.

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-22 : 00:11:55

[code]insert table2
select ip,service,hit,
(select count (*)
from
(select ip,sum(hit) hit
from table1
group by ip,service
) b
where b.ip=a.ip and b.hit >=a.hit) rank1,
(select count (*)
from
(select ip,service,sum(hit) hit
from table1
group by ip,service
) b
where b.ip=a.ip and b.hit >=a.hit) rank2
from
(select ip,service,sum(hit) hit
from table1
group by ip,service
) as a[/code]

maven_9999
Starting Member

7 Posts

Posted - 2004-11-22 : 02:02:21

Hi,

Thanks for your quick response. I have tried with your query, gtting below results.

IP - SERVICE - HIT - RANK1 - RANK2

1.1.1.1 - TCP - 300 - 2 - 2
1.1.1.1 - UDP - 400 - 1 - 1
2.2.2.2 - TCP - 150 - 1 - 1
2.2.2.2 - UDP - 100 - 2 - 2

Query generating Rank2 correct result, but Rank1 should be like below.

IP - SERVICE - HIT - RANK1 - RANK2

1.1.1.1 - TCP - 300 - 1 - 2
1.1.1.1 - UDP - 400 - 1 - 1
2.2.2.2 - TCP - 150 - 2 - 1
2.2.2.2 - UDP - 100 - 2 - 2

I have tried to change and run query but not getting expected result. As this is little urgent for me, if you can help to get Rank1 as above will be great.

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-22 : 02:12:21

you're criteria is not consistent with what your table 2 result or probably i'm not reading this correctly

declare @t table(IP varchar(20),SERVICE varchar(3),HIT int)
declare @r1 table(rank int identity(1,1),IP varchar(20),HIT int)
declare @r2 table(rank int identity(1,1),SERVICE varchar(3),HIT int)

insert into @t
select '1.1.1.1' , 'UDP' , 200
insert into @t
select '1.1.1.1' , 'TCP' , 300
insert into @t
select '1.1.1.1' , 'UDP' , 200
insert into @t
select '2.2.2.2' , 'UDP' , 100
insert into @t
select '2.2.2.2' , 'TCP' , 50
insert into @t
select '2.2.2.2' , 'TCP' , 100

insert into @r1(ip,hit)
select ip,sum(hit) as [hit]
from @t
group by ip
order by hit desc

insert into @r2(service,hit)
select service,sum(hit) as [hit]
from @t
group by service
order by hit desc

select t.ip,t.service,sum(t.hit) as [hit],r1.rank,r2.rank
from @t t
join @r1 r1 on t.ip=r1.ip
join @r2 r2 on t.service=r2.service
group by t.ip,t.service,r1.rank,r2.rank

--------------------
keeping it simple...

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-22 : 12:38:20

Sorry, It's my mistake (:

insert table2
select r2.ip,r2.service,r2.hit,
(select count (*) 
 from
    (select ip,sum(hit) hit 
     from  table1
     group by ip
    ) b
 where b.hit >=(select sum(hit) from  table1 where table1.ip=r2.ip)) rank1,
(select count (*) 
 from
    (select ip,service,sum(hit) hit 
     from  table1
     group by ip,service
    ) b
 where b.ip=r2.ip and b.hit >=r2.hit) rank2
from 
 
   (select ip,service,sum(hit) hit 
    from  table1
    group by ip,service
    ) as r2

maven_9999
Starting Member

7 Posts

Posted - 2004-11-22 : 20:24:31

Hi,

Thanks for this query, I really appreciate your help. The Query which you is working perfect and getting correct results. But when I am trying same query with some huge DB, query is taking little toomuch time. Please suggest, is there any other way to get better performance than this.

Thanks again for this query.

maven_9999
Starting Member

7 Posts

Posted - 2004-11-22 : 20:44:22

hi,

sorry, this is continuation to above post. In table1 I will be having around 650000 records, but I have to take only top 100 (sum(hit) group by ip) records from that. At present above query which you gave is taking 30+ minutes to insert into table2.

Thanks.

maven_9999
Starting Member

7 Posts

Posted - 2004-11-23 : 09:48:45

Hi,

I am looking for an update statement. As I tried with creating temporary tables as you said in previous reply. But performance is too poor when i was working with large DB. I can able to get Rank1 with very good speed with below query.

create table ranking1
( rank1 integer not null primary key auto_increment
, ip varchar(15)
, hit integer
);
insert into ranking1 (ip, hit) select ip, sum(hit) as h from table1 group by ip order by h desc limit 100;

insert into table2 (user_ip,service,hit,rank1)
select table1.ip
, table1.protoid
, table1.hit
, ranking1.rank1
from table1
inner
join ranking1
on table1.ip
= ranking1.ip
order
by ranking1.rank1;

But when I am trying to insert Rank2, performance going low. So thought to perform an update statement on resulted table.

I think I have not given requirement properly, below query, which you gave in ur previous mail is generating rank2 for all records. Instead it should give ranks on "group by servie and ip=ip" (rank for one particular ip). For that I tried to do sub query to get rank2 as you specified above. using sub query performance is going down.

"insert into @r2(service,hit)
select service,sum(hit) as [hit]
from @t
group by service
order by hit desc" ---- This is giving ranks for all services in table, instead it should give for particular ip.

Sorry to giving you trouble, by asking again.

plz help me to get an update query for above.

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources