sql - Select most recent date + inner join -

i trying query recent scan date (the 2 recent ones 3/5/2012 , 3/1/2012 ... i'd return records 3/5/2012, need automated every time new scan run query pulling recent date.

i'm joining 2 tables ip. put query below , receiving error "please check sql syntax. column qryreportscondensedpatchesaggregate.scanname invalid in select list because not contained in either aggregate function or group clause."

select qryreportscondensedpatchesaggregate.scanname,     qryreportscondensedpatchesaggregate.pspplmsseverity,     qryreportscondensedpatchesaggregate.smachipaddress,     qryreportsscansummarywithdetailsaggregate.patchmissing,      max(qryreportscondensedpatchesaggregate.scandate) qryreportscondensedpatchesaggregate inner join qryreportsscansummarywithdetailsaggregate on qryreportscondensedpatchesaggregate.smachipaddress=qryreportsscansummarywithdetailsaggregate.smachipaddress qryreportscondensedpatchesaggregate.scanname '%mgmt%'      , qryreportscondensedpatchesaggregate.scandate>= dateadd(day,-30,getdate()) order qryreportscondensedpatchesaggregate.scandate desc 

edit: here sample data of being returned:

column1 smachipaddress   scanname   pspplmsseverity patchmissing  3/5/2012 15:24  162.1.1.1   mgmt servers ou 0   1 3/5/2012 15:24  162.1.1.1   mgmt servers ou 0   1 3/5/2012 15:24  1162.1.1.1  mgmt servers ou 0   1 3/5/2012 15:24  162.1.1.1   mgmt servers ou 0   2 3/5/2012 15:24  162.1.1.1   mgmt servers ou 0   1 3/1/2012 17:29  162.1.1.1   mgmt servers ou 0   2 3/1/2012 17:29  162.1.1.1   mgmt servers ou 0   4 3/1/2012 17:29  162.1.1.1   mgmt servers ou 0   2 3/1/2012 17:29  162.1.1.1   mgmt servers ou 0   3 3/1/2012 17:29  162.1.1.1   mgmt servers ou 0   1 3/1/2012 17:29  162.1.1.1   mgmt servers ou 0   3 

first table:

table_name  ordinal_position    column_name data_type   is_nullable character_maximum_length qryreportscondensedpatchesaggregate 1   machinegroup    nvarchar    yes 276 qryreportscondensedpatchesaggregate 2   scanid  int no   qryreportscondensedpatchesaggregate 3   scanuser    nvarchar    yes 4000 qryreportscondensedpatchesaggregate 4   scandate    datetime    no   qryreportscondensedpatchesaggregate 5   scanname    nvarchar    yes 255 qryreportscondensedpatchesaggregate 6   itemtype    int yes  qryreportscondensedpatchesaggregate 7   smachid int no   qryreportscondensedpatchesaggregate 8   smachname   nvarchar    no  255 qryreportscondensedpatchesaggregate 9   smachipaddress  nvarchar    yes 39 qryreportscondensedpatchesaggregate 10  smachdomainname nvarchar    yes 255 qryreportscondensedpatchesaggregate 11  smachipdecimal  float   yes  qryreportscondensedpatchesaggregate 12  smachannotation ntext   yes 1073741823 qryreportscondensedpatchesaggregate 13  smachnotfoundreasonerrornum smallint    yes  qryreportscondensedpatchesaggregate 14  smachservertype int yes  qryreportscondensedpatchesaggregate 15  mmosid  int yes  qryreportscondensedpatchesaggregate 16  patchbulletinid nvarchar    no  255 qryreportscondensedpatchesaggregate 17  patchbulletinurl    nvarchar    yes 4000 qryreportscondensedpatchesaggregate 18  patchqnumbers   nvarchar    no  255 qryreportscondensedpatchesaggregate 19  patchlevel  int yes  qryreportscondensedpatchesaggregate 20  pspplmsseverity int yes  qryreportscondensedpatchesaggregate 21  pspplcriticality    int yes  qryreportscondensedpatchesaggregate 22  pspplannotation nvarchar    yes 4000 qryreportscondensedpatchesaggregate 23  prodid  int no   qryreportscondensedpatchesaggregate 24  spplspid    int no   qryreportscondensedpatchesaggregate 25  scantype    int yes 

2nd table:

table_name  ordinal_position    column_name data_type   is_nullable character_maximum_length qryreportsscansummarywithdetailsaggregate   1   scanid  int no   qryreportsscansummarywithdetailsaggregate   2   scantype    int yes  qryreportsscansummarywithdetailsaggregate   3   scanname    nvarchar    yes 255 qryreportsscansummarywithdetailsaggregate   4   scandate    datetime    no   qryreportsscansummarywithdetailsaggregate   5   scanuser    nvarchar    yes 4000 qryreportsscansummarywithdetailsaggregate   6   consoleid   int yes  qryreportsscansummarywithdetailsaggregate   7   consolename nvarchar    yes 255 qryreportsscansummarywithdetailsaggregate   8   smachname   nvarchar    no  255 qryreportsscansummarywithdetailsaggregate   9   smachipaddress  nvarchar    yes 39 qryreportsscansummarywithdetailsaggregate   10  scantname   nvarchar    yes 255 qryreportsscansummarywithdetailsaggregate   11  firstpatchgroupname nvarchar    yes 255 qryreportsscansummarywithdetailsaggregate   12  countpatchgroups    int yes  qryreportsscansummarywithdetailsaggregate   13  smachagent  smallint    no   qryreportsscansummarywithdetailsaggregate   14  smachdomainname nvarchar    yes 255 qryreportsscansummarywithdetailsaggregate   15  smachipdecimal  float   yes  qryreportsscansummarywithdetailsaggregate   16  smachid int no   qryreportsscansummarywithdetailsaggregate   17  smachlisttype   int yes  qryreportsscansummarywithdetailsaggregate   18  scanxmlversion  nvarchar    yes 50 qryreportsscansummarywithdetailsaggregate   19  scanxmldate datetime    yes  qryreportsscansummarywithdetailsaggregate   20  itemlanguageid  nvarchar    yes 4 qryreportsscansummarywithdetailsaggregate   21  patchfoundorinstalled   int yes  qryreportsscansummarywithdetailsaggregate   22  patchmissing    int yes  qryreportsscansummarywithdetailsaggregate   23  servicepackmissing  int yes  qryreportsscansummarywithdetailsaggregate   24  countofmachinesscanned  int no   qryreportsscansummarywithdetailsaggregate   25  countofmachinesnotscanned   int no   qryreportsscansummarywithdetailsaggregate   26  totalmachines   int yes  qryreportsscansummarywithdetailsaggregate   27  scanfoundpatches    int yes  qryreportsscansummarywithdetailsaggregate   28  scanmissingpatches  int yes  qryreportsscansummarywithdetailsaggregate   29  scanservicepackmissing  int yes  qryreportsscansummarywithdetailsaggregate   30  countofgroups   int yes  qryreportsscansummarywithdetailsaggregate   31  grpname nvarchar    yes 255 

your error telling problem, if using aggregate function max(), sum(), etc fields not using aggregate must included in group by.

below query looks aliases , group by

select cpa.scanname,     cpa.pspplmsseverity,     cpa.smachipaddress,     sda.patchmissing,      max(cpa.scandate) qryreportscondensedpatchesaggregate cpa inner join qryreportsscansummarywithdetailsaggregate sda     on cpa.smachipaddress=sda.smachipaddress cpa.scanname '%mgmt%'      , cpa.scandate>= dateadd(day,-30,getdate()) group cpa.scanname, cpa.pspplmsseverity, cpa.smachipaddress     , sda.patchmissing, cpa.scandate order cpa.scandate desc