I believe that “datecolumn” should not be in the GROUP BY clause.
Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: [email protected] [mailto:[email protected]] On Behalf Of Michael J. Sinclair Sent: October-13-15 8:07 AM To: Stephen Markson Subject: [RBASE-L] - RE: extracting the most recent data Thanks Buddy! I had the syntax wrong, yours works perfectly. Mike ________________________________ From: Buddy Walker <[email protected]<mailto:[email protected]>> To: [email protected]<mailto:[email protected]> Sent: Monday, October 12, 2015 8:54 PM Subject: [RBASE-L] - RE: extracting the most recent data Mike You could try SELECT MAX(datecolumn), patientnumber,testcode from sometable group by patientnumber, testcode, datecolumn Buddy From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Michael J. Sinclair Sent: Monday, October 12, 2015 8:23 PM To: [email protected]<mailto:[email protected]> Subject: [RBASE-L] - extracting the most recent data Hi all I have a very long skinny table, 3 columns. A date, a patient number and test code. I want to extract the rows that are the most recent test code for each patient. If patient #3000 has had testcode # 80015 10 times over the last year, then I only want the most recent row for that testcode/patient combination Is there a single command that can do that? Mike
