Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    17

    Unanswered: Need a Query to update ranks....

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    17

    please help me to get code

    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •