Try this.
------------------------------------------------------------------
SET VAR vCount INTEGER = NULL
SELECT COUNT(*) INTO vCount FROM sys_tables +
WHERE sys_table_name = 'MostRecentTests'
IF vCount > 0 THEN
DROP TABLE MostRecentTests
ENDIF
Create TEMP TABLE MostRecentTests +
(PatientNumber INTEGER, TestCode INTEGER, TestDate DATE)
INSERT INTO MostRecentTests (PatientNumber, TestCode, TestDate) +
SELECT PatientNumber, TestCode, (MAX(TestDate)) +
FROM VeryLongSkinnyTable +
GROUP BY PatientNumber, TestCode
------------------------------------------------------------------
If this is for a report, I would usually put the desired information
such as the patient name and test name columns into the temporary table
and do update the table rather than using report variables. The
procedure will run faster.
Albert
On 2015-10-12 7:41 PM, Mike wrote:
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]
<mailto:[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]
<mailto:[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
