Hi ,

I have a table with data like below.

IP - service - hit -rank1-rank2
1.1.1.1 - Udp - 400
1.1.1.1 - Tcp - 300
2.2.2.2 - Udp - 100
2.2.2.2 - Tcp - 150

by default rank1, rank2 will be null. Need to update ranks and resulted table as looks like....

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 - 2 - 1

rank1 should be calculated like ....
sum(hit) from table group by ip.

and rank2 should be calculated like...

sum(hit) from table group by service where ip=ip.

Rank1 will be max hits to one particular ip, no matter whatever service is.

Rank2 will be max of hits to one particular service, for one ip.

Please help me to write update query for above. Its little urgent for me.

Thanks.

want me to try? you will have to create a couple of temp tables

hmm, lemme see, i've got that code around here somewhere

Hi,

can you please help me to get that code or guide me how to proceed further. As its little urgent for me, tried in different ways, but nothing was worked out till.

Thanks.

try this --

http://www.dbforums.com/t1063783.html