sql - Subquery for max ID numbers -

i have query trying filter report. each addressid can have multiple jobs , each job can have multiple elements it.

basically trying maximum jobid each addressid, want each element of job.

the current query results are:

+-----------+-------+--------+ | 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 | +-----------+-------+--------+

and trying get:

+-----------+-------+--------+ | 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 | +-----------+-------+--------+

i had been looking @ select top 1 isolate max jobid, ended limiting query 1 entry.

currently tweaking subquery, still not sure i'm on right track:

(select max(vusearch.jobid) vusearch t paid = vusearch.addressid group addressid) 

can advise?

here 1 method:

select v.* vusearch v v.jobid = (select max(v2.jobid)                  vusearch v2                  v2.addressid = v.addressid                 );