Find newest value in mysql database -

Find newest value in mysql database -

i have 2 tables store infotmation images uploaded site phone call image , audit. when image uploaded it's path stored in image.imagelocation , when used audit auditid saved same table. auditid value used joining tables together.

i trying create query tell me latest audit image used using date audit made audit.datestart.

the query have far is...

select * ( select image.imageid newest_image, image.userid image_userid, image.auditid image_auditid, image.type image_type, image.imagelocation location image ) image left bring together audit on image_auditid = audit.auditid image_userid = '$id' , image_type = 'standard' , datestart = max(datestart) grouping location

this throws error saying improper utilize of group.

any help gratefully received

this table structure

audit auditid varchar(255) userid int(11) datestart date dateend date auditname varchar auditdesc varchar imagesuploaded tinyint actionplan varchar auditcomplete tinyint datecomplete datetime image imageid int(10) name varchar(255) auditid varchar(255) imagegrade int(1) exposure varchar(255) positioning varchar(255) patient varchar(255) equipment varchar(255) positioningreason varchar(255) exposurereason varchar(255) patientreason varchar(255) comment varchar(255) auditdate datetime imagelocation varchar(255) thumbnail text xraytype varchar(10) type varchar(255) userid int(11) sample info imageid = 563 name = standard_16.jpg auditid = 1360318160843025059 imagelocation = upload/2/standards/standard_16.jpg type = standard userid = 2 imageid = 565 name = standard_18.jpg auditid = 1360318160843025059 imagelocation = upload/2/standards/standard_18.jpg type = standard userid = 2 imageid = 566 name = standard_18.jpg auditid = 13603248241777655575 imagelocation = upload/2/standards/standard_18.jpg type = standard userid = 2 auditid = 1360318160843025059 userid = 2 datestart = 2013-02-14 auditid = 13603248241777655575 userid = 2 datestart = 2013-02-09

as can see same images can used more 1 audit , want know audit used image last, in case result auditid 1360318160843025059

have tried using subquery. subquery max(datestart) each audit. bring together result audit table homecoming of details of row max date:

select i.imageid newest_image, i.userid image_userid, i.auditid image_auditid, i.type image_type, i.imagelocation location, a1.* image left bring together audit a1 on i.auditid = a1.auditid inner bring together ( select auditid, max(datestart) maxdate audit grouping auditid ) a2 on a1.auditid = a2.auditid , a1.datestart = a2.maxdate i.userid = '$id' , i.type = 'standard'

edit, if image/audit last, can use:

select i.imageid newest_image, i.userid image_userid, i.auditid image_auditid, i.type image_type, i.imagelocation location, a1.* image left bring together audit a1 on i.auditid = a1.auditid inner bring together ( select max(datestart) maxdate audit ) a2 on a1.datestart = a2.maxdate i.type = 'standard';

see sql fiddle demo

just guess:

select i.imageid newest_image, i.userid image_userid, i.auditid image_auditid, i.type image_type, i.imagelocation location, a.* image bring together audit on i.auditid = a.auditid bring together ( select i.imagelocation, max(a.datestart) maxdate image bring together audit on i.auditid = a.auditid i.userid = '$id' , i.type = 'standard' grouping i.imagelocation ) g on g.maxdate = a.datestart , g.imagelocation = i.imagelocation i.userid = '$id' , i.type = 'standard'

mysql