title: Refining the output from LAG query
body: <p>Hello I'm running postgresql 10.10 on Ubuntu.</p>
  
  <p>I have the following data in my table:</p>
  
  <pre><code>  radio  | hits | iteration
  ---------+------+-----------
       211 |    1 |         1
   1423013 |    1 |         1
   2114805 |    1 |         1
   2114833 |    1 |         1
   2109257 |    1 |         1
    126681 |    1 |         2
    802802 |    2 |         2
   1426801 |    2 |         2
   2613847 |    1 |         2
       208 |   27 |         2
  (10 rows)
  </code></pre>
  
  <pre><code> radio | hits | iteration
  -------+------+-----------
     211 |    1 |         1
     211 |   24 |         2
     211 |   49 |         3
     211 |   84 |         4
     211 |  131 |         5
     211 |  157 |         6
     211 |  169 |         7
     211 |  181 |         8
     211 |  200 |         9
     211 |  223 |        10
     211 |  244 |        11
     211 |  273 |        12
  </code></pre>
  
  <p>The hits column is cumulative, I need to subtract the previous iterations hits from the current iterations hits to determine the change in value. Currently I'm using the following LAG query.</p>
  
  <pre><code>SELECT radio_alias, iteration, radio, hits - LAG ( hits, 1, 0 ) 
  OVER (PARTITION BY radio ORDER BY iteration) AS ithits FROM radios;
  
  </code></pre>
  
  <p>This gives me the following output, which is what I expect:</p>
  
  <pre><code> radio_alias | iteration |  radio  | ithits
  -------------+-----------+---------+--------
   ""          |       101 |     205 |      0
   ""          |       102 |     205 |      7
   ""          |       103 |     205 |      2
   ""          |         2 |     208 |     27
   ""          |         3 |     208 |     38
   ""          |         4 |     208 |     13
   ""          |         5 |     208 |     21
  
  
  </code></pre>
  
  <p><strong>What I need to do is select all rows with the largest iteration value from the output of the LAG query, ideally the SQL statement would determine it automatically, something like MAX(iteration), if not I can provide a variable from my program.</strong></p>
  
  <p>I've been able to solve it using temp tables using the following code but was wondering if there was a more elegant solution not involving temp tables.</p>
  
  <pre><code>SELECT radio_alias, iteration, radio, hits - LAG ( hits, 1, 0 ) 
  OVER (PARTITION BY radio ORDER BY iteration) AS ithits INTO TEMP rad_hits FROM radios;
  
  </code></pre>
  
  <pre><code>SELECT * FROM rad_hits WHERE iteration = 103 ORDER BY ithits DESC LIMIT 5;
  </code></pre>
  
  <pre><code> radio_alias | iteration |  radio  | ithits
  -------------+-----------+---------+--------
   ""          |       103 |     209 |     41
   ""          |       103 |     211 |     29
   ""          |       103 | 2109215 |     20
   ""          |       103 |     210 |     18
   ""          |       103 | 2109232 |     17
  (5 rows)
  </code></pre>