Will SELECT PatNumbr, Serivce, MAX(D_O_S) FROM OldDets GROUP BY PatNumbr, Service
not give you what you want? -- Larry ________________________________ From: Michael J. Sinclair <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Sun, October 25, 2009 7:04:04 PM Subject: [RBASE-L] - Is there a better way to get the list of most recent items? Hi all, My goal is find out when each test was peformed the last time on a patient. I have a table with the name of the test (column name is service) and the date when it was performed (date of service column is called d_o_s_) To do the job I dumped the data from a gigantic table called olddets to an ascii file, but I did sort the result by the date, most recent first. Then I loaded the data from the ascii file into a table that has rule that says the column service has to be unique. That way, the most recent row gets added to the table, and all of the other rows with the same service with older dates don't get added...because they have the same data in the column called service...which has to be unique. The unique id for each patient is the column patnumbr, the variable that determines which patient I want is called vpnum. The code that worked in Rbase 6.5 that was working as follows... DEL FRO LASTTEST_TMP OUTPUT C:\LASTTEST UNLOAD DATA FOR OLDDETS USING PATNUMBR D_O_S_ SERVICE AS + ASCII ORDER BY D_O_S_ = D WHE PATNUMBR = .VPNUM OUTPUT SCREEN SET MESSAGES OFF SET ERROR MESSAGES OFF SET BELL OFF LOAD LASTTEST_TMP FROM C:\LASTTEST AS ASCII OUTPUT SCREEN BRO D_O_S_ SERVICE FROM LASTTEST_TMP SORT BY SERVICE This code does not work in Rbase 7.6. It gives me an error that the Service is not unique, despite the fact that I set the error messages off. Is there a better/faster way to do this? TIA! Mike
