title: Subquery for max ID numbers
body: <p>I have a query that I am trying to filter for a report.  Each addressID can have multiple jobs and each job can have multiple elements to it.</p>
  
  <p>Basically I am trying to get the maximum jobID for each addressID, but I want to get each element of the job.</p>
  
  <p>The current Query results are:</p>
  
  <pre>+-----------+-------+--------+
  | AddressID | JobID | Cost   |
  +-----------+-------+--------+
  | 326       | 328   | £52.50 |
  | 327       | 329   | £55.13 |
  | 328       | 330   | £57.88 |
  | 329       | 331   | £60.78 |
  | 329       | 331   | £63.81 |
  | 330       | 332   | £67.00 |
  | 330       | 332   | £70.36 |
  | 330       | 332   | £73.87 |
  | 330       | 332   | £77.57 |
  | 330       | 333   | £57.75 |
  | 330       | 333   | £60.64 |
  | 330       | 333   | £63.67 |
  | 330       | 333   | £66.85 |
  | 331       | 334   | £70.20 |
  | 331       | 334   | £73.71 |
  | 331       | 335   | £77.39 |
  | 331       | 336   | £81.26 |
  | 331       | 336   | £85.32 |
  | 331       | 336   | £89.59 |
  +-----------+-------+--------+</pre>
  
  <p>And I am trying to get:</p>
  
  <pre>+-----------+-------+--------+
  | AddressID | JobID | Cost   |
  +-----------+-------+--------+
  | 326       | 328   | £52.50 |
  | 327       | 329   | £55.13 |
  | 328       | 330   | £57.88 |
  | 329       | 331   | £60.78 |
  | 329       | 331   | £63.81 |
  | 330       | 333   | £57.75 |
  | 330       | 333   | £60.64 |
  | 330       | 333   | £63.67 |
  | 330       | 333   | £66.85 |
  | 331       | 336   | £81.26 |
  | 331       | 336   | £85.32 |
  | 331       | 336   | £89.59 |
  +-----------+-------+--------+</pre>
  
  <p>I had been looking at SELECT TOP 1 to isolate the MAX JobID, but ended up limiting the query to just 1 entry.</p>
  
  <p>Currently tweaking this subquery, but still not sure I'm on the right track:</p>
  
  <pre><code>(SELECT Max(vusearch.JobID) FROM vuSearch AS T WHERE PAID = vuSearch.AddressID GROUP BY AddressID)
  </code></pre>
  
  <p>Can anyone advise?</p>