title: BigQuery group by maximum value in one field while selecting distinct rows
body: <p>For every 20 minute period interval, I am trying to find the maximum value of bandwidth (column <code>mbps</code> in the table below), each of the unique IP addresses generates, with the corresponding port number.</p>
  
  <p>Each IP address may or may not show up more than once in each 20-minute period. Each time an IP address gets recorded in the 20-minute period interval, it may or may not have the same port number listed.</p>
  
  <p>For example, in the table below, the ip address 192.168.10.1 shows up three times during the period listed as 12:20, with port numbers 443, 80 and 80 respectively. In another scenario, the ip address 192.168.10.2 shows up twice during the period 12:40, with the same port number 443, listed twice, but with different values for the mbps (bandwidth) column. </p>
  
  <p>The goal is to select and list each unique ip address only once, for each of the twenty minute period, sorting by mbps in DESC order.</p>
  
  <p>The table is partitioned, based on the time of data injection. </p>
  
  <p>I am planning to write a cron job, to automate this query. The cron job will run every hour, 7 days a week. The query is to be in standard SQL.</p>
  
  <p>original table:</p>
  
  <pre><code>Row time                ip_address          port        mbps
  
  1   01/01/2019 12:20    192.168.10.1        443         100
  2   01/01/2019 12:20    192.168.10.1        80          120
  3   01/01/2019 12:20    192.168.10.2        80          200
  4   01/01/2019 12:20    192.168.10.1        80          110
  5   01/01/2019 12:40    192.168.10.2        443         200
  6   01/01/2019 12:40    192.168.10.3        443         300
  7   01/01/2019 12:40    192.168.10.2        443         200
  8   01/01/2019 12:40    192.168.10.1        443         300
  9   01/01/2019 13:00    192.168.10.3        443         90
  10  01/01/2019 13:00    192.168.10.2        80          100
  11  01/01/2019 13:00    192.168.10.1        443         500
  </code></pre>
  
  <p>executing the code below,</p>
  
  <pre><code>#standardSQL
  SELECT
    FORMAT_TIMESTAMP("%d/%m/%Y %H:%M", TIMESTAMP_SECONDS, 'Europe/London') AS time,
    ip_address,
    port,
    SUM(bandwidth) AS mbps,
  FROM
    dataset1.table1
  WHERE
    _PARTITIONDATE = DATE_SUB(CURRENT_DATE(),INTERVAL 0 DAY)  
    AND timestamp &gt; TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL -40 MINUTE)
  GROUP BY
    time,
    ip_address,
    port
  ORDER BY
    time,
    mbps DESC
  </code></pre>
  
  <p>I get this table,</p>
  
  <pre><code>Row time                ip_address          port        mbps
  
  1   01/01/2019 12:20    192.168.10.2        80          200
  2   01/01/2019 12:20    192.168.10.1        80          120
  3   01/01/2019 12:20    192.168.10.1        80          110
  4   01/01/2019 12:20    192.168.10.1        443         100
  5   01/01/2019 12:40    192.168.10.1        443         300
  6   01/01/2019 12:40    192.168.10.3        443         300
  7   01/01/2019 12:40    192.168.10.2        25          200
  8   01/01/2019 12:40    192.168.10.2        443         160
  9   01/01/2019 13:00    192.168.10.1        443         500
  10  01/01/2019 13:00    192.168.10.2        80          100
  11  01/01/2019 13:00    192.168.10.3        443         90
  </code></pre>
  
  <p>which is not what I want. Instead, I want this:</p>
  
  <pre><code>Row time                ip_address          port        mbps
  
  1   01/01/2019 12:20    192.168.10.2        80          200
  2   01/01/2019 12:20    192.168.10.1        80          120
  3   01/01/2019 12:40    192.168.10.1        443         300
  4   01/01/2019 12:40    192.168.10.3        443         300
  5   01/01/2019 12:40    192.168.10.2        25          200
  6   01/01/2019 13:00    192.168.10.1        443         500
  7   01/01/2019 13:00    192.168.10.2        80          100
  8   01/01/2019 13:00    192.168.10.3        443         90
  </code></pre>
  
  <p>What am I doing wrong?</p>