Ajit, No that won't work. I assume that Alan wants the Pay and Tax associated with the maximum period record for each employee.
Can't stop as I'm in a meeting in minus 5 mins...If you haven't had an answer before then I'll post one. Answer off the top pof my head is below: Select Paytable.* ; >From PayTable ; Join (select Employeecode, Max(Period) as Period From Paytable Group By EmployeeCode) XX On XX.Period=Paytable.Period And XX.EmployeeCode=Paytable.EmployeeCode Dave Crozier -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ajit Abraham Sent: 18 June 2008 12:42 To: [email protected] Subject: Re: SQL Select - you think for me, I have a head cold. Hi Alan, Assuming that the "latest period" is unique for each employee, select employeecode,; max(periodcode) as periodcode, ; max(pay) as pay, ; max(tax) as tax ; from paytable ; group by employeecode _Ajit Abraham Alan Bourke wrote: > VFP=9 SP1 > EngineBehavior=90 > > I have a table with employeecode, period, pay, tax. > > The period is in the form '200803' for 2008 period 3. > > I want one row for each employee, for the latest period available for > that employee, with the pay and tax fields. > > Which is easy enough to start: > > Select employeecode, Max(periodcode) as periodcode From paytable Order > By employeeco Group By employeeco > > ... but once I add the pay and tax fields it wants them in the GROUP BY > as well. This is undoubtedly simple but the pixies* are kicking the > inside of my head and I can't think. > > > > * The little fellas like leprechauns, not the popular alt-rockers from > Boston. > > __________________________________________________ D O T E A S Y - "Join the web hosting revolution!" http://www.doteasy.com [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
