Max values in a select statement

[tcronin]

I have a table which has 4 fields, patientid,testdate,testtype,results.
I want to select the most recent testdate by a patient regardless
of the results, or the testtype. I do however need those fields for my query.
I tried the below, but I get more than 1 record if the person has had two different types of tests. For instance if patient 100 has the following 2 records I just want the most recent

patientid testdate testype results

100 01/02/2002 TBI ASYMP
100 02/02/2001 PPD 00000

select max(testdate)as testdate,testtype, other_id_number
from vw_cms_tb_lasttest

What am missing?
Thanks
group by other_id_number,testtype
order by other_id_number,testtype

[Martyn Hodgson]

The way I would do it would be something like this:

select a.whatever
from vw_cms_tb_lasttest a
where a.testdate = (select max(b.testdate) from vw_cms_tb_lasttest b)

If you need to apply other predicates make sure you apply them to both the inner and outer select statement.

Regards

Martyn


------------
tcronin at 5/22/2002 6:08:30 PM


I have a table which has 4 fields, patientid,testdate,testtype,results.
I want to select the most recent testdate by a patient regardless
of the results, or the testtype. I do however need those fields for my query.
I tried the below, but I get more than 1 record if the person has had two different types of tests. For instance if patient 100 has the following 2 records I just want the most recent

patientid testdate testype results

100 01/02/2002 TBI ASYMP
100 02/02/2001 PPD 00000

select max(testdate)as testdate,testtype, other_id_number
from vw_cms_tb_lasttest

What am missing?
Thanks
group by other_id_number,testtype
order by other_id_number,testtype