How do I get that information for all patients and all tests at the same time? Ideally what I would like to do is to append the most recent rows, for each patient/test combo from this table to a separate table. I think there is supposed to be way of doing this using the group by but I don't understand how to combine a group by with the max function.
> On Oct 12, 2015, at 8:48 PM, Bill Downall <[email protected]> > wrote: > > Select * from VeryLongSkinnyTable + > Where patientNumber = .vpatientnumber + > And datecolumn = + > (Select max (datecolumn) + > From VeryLongSkinnyTable + > Where patientNumber = .vpatientnumber) > > Since you don't have a timestamp or sequence column, if there are two test > codes or more on the most recent date, you will get more than one result. > > Bill > > > (sent from Moto X2) > > On Oct 12, 2015 8:26 PM, "Michael J. Sinclair" <[email protected]> wrote: > 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
