Query Against Temp Table not Working (includes Max Function)

• LoosinMaMind

  Hall of Fame

  Points: 3366

  More actions

  September 17, 2010 at 5:08 am

  #229401

  Hi All

  Running a simple select from a tempary table created from query. Trying to link 3 tables and on one of them just link on the max value from a selection of dupplicate rows. (disepisode being the field with maximum values)

  SELECT PV_Readmissions.admission, PV_Readmissions.disdate, PV_Readmissions.crn, PV_Readmissions.EXPR1, PV_Readmissions.admdate,

  PV_Readmissions.admtime, PV_Readmissions.EXPR2, PV_Readmissions.admsource, PV_Readmissions.admtype, PV_Readmissions.disepisode,

  PV_Readmissions.readmit

  FROM PV_Readmissions LEFT OUTER JOIN

  medrec ON PV_Readmissions.crn = medrec.crn LEFT OUTER JOIN

  adm_tfer ON PV_Readmissions.crn = adm_tfer.crn AND PV_Readmissions.admission = adm_tfer.admission AND

  PV_Readmissions.disepisode = adm_tfer.episode

  WHERE (PV_Readmissions.disepisode = MAX(PV_Readmissions.disepisode))

  I get the following error message

  Msg 147, Level 15, State 1, Line 9

  An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

  Any ideas anyone, Thanks in advance for replies

• sanmatrix

  SSC Veteran

  Points: 250

  More actions

  September 17, 2010 at 6:33 am

  #1222922

  SELECT PV_Readmissions.admission, PV_Readmissions.disdate, PV_Readmissions.crn, PV_Readmissions.EXPR1, PV_Readmissions.admdate,

  PV_Readmissions.admtime, PV_Readmissions.EXPR2, PV_Readmissions.admsource, PV_Readmissions.admtype, PV_Readmissions.disepisode,

  PV_Readmissions.readmit

  FROM PV_Readmissions LEFT OUTER JOIN

  medrec ON PV_Readmissions.crn = medrec.crn LEFT OUTER JOIN

  adm_tfer ON PV_Readmissions.crn = adm_tfer.crn AND PV_Readmissions.admission = adm_tfer.admission AND

  PV_Readmissions.disepisode = adm_tfer.episode

  WHERE (PV_Readmissions.disepisode = MAX(PV_Readmissions.disepisode))

  Look at the bold section MAX is an aggregate function and therefore you can not use it in where clause. instead use a sub query like

  SELECT MAX

  (PV_Readmissions.disepisode) FROM tablename

• LoosinMaMind

  Hall of Fame

  Points: 3366

  More actions

  September 18, 2010 at 1:25 pm

  #1223499

  thanks for your reply. all sorted now.cheers.