How do I retrieve only the row with the most recent date?

[Ginny28]

I am running the following query and need to modify it to pull only 1 row, with the most recent Onset Date, when the pr.diagnosis_co de_id and pr.description are not unique. I could actually use one or the other, since the code always maps to the same description.

Currently, my query outputs:

Acute cholecystitis, 575.0, 06/29/2010, --, --
Acute pancreatitis, 577.0, 06/08/2010, --, --
Acute posthemorrhagic anemia, 285.1, 06/29/2010, --, --
Bipolar Disoder, 296.80, --, --, 06/29/2010
Bipolar Disoder, 296.80, 06/08/2010, --, 06/29/2010
ETOH Dependence, 303.91, 06/08/2010, --, --

And I want it to output the following (removing the Bipolar Disoder where there the Onset date is the least current), but I don't know how to do a "distinct" on the pr.description or pr.diagnosis_co de_id:

Acute cholecystitis, 575.0, 06/29/2010, --, --
Acute pancreatitis, 577.0, 06/08/2010, --, --
Acute posthemorrhagic anemia, 285.1, 06/29/2010, --, --
Bipolar Disoder, 296.80, 06/08/2010, --, 06/29/2010
ETOH Dependence, 303.91, 06/08/2010, --, --

Here is my query:
select
pr.diagnosis_co de_id,
pr.description,
pr.date_onset_s ympt as dt1,
pr.date_diagnos ed as dt2,
pr.date_resolve d as dt3,
pe.enc_timestam p as dt4
from patient p,
patient_diagnos is pr,
patient_encount er pe
where p.med_rec_nbr = $mrn
and pr.person_id = p.person_id
and pr.practice_id = $practice_id
and pr.enterprise_i d = $enterprise_id
and pe.enc_id = pr.enc_id
order by pr.description, pe.enc_timestam p DESC

Thanks! I am new to this and really struggle once I get out of the simple select stuff!

[Brad Orders]

Hi Ginny28

Here is some example code for tackling your problem.
This is what I use as a starting point whenever I am working on the type of problem you are describing.

Hope it helps!

Kind regards


Brad



declare @TestData Table
(
RecordId INT IDENTITY(1,1),
STUDENT_ID VARCHAR(6),
TF Decimal(2,1)
)

insert into @TestData (student_id, tf)
select 'abc123', 0.2 UNION
select 'abc123', 0.7 UNION
select 'abc123', 0.4 UNION
select 'def123', 0.1 UNION
select 'def123', 0.5 UNION
select 'def123', 0.4

select * from @testdata

select * from
@TestData Data
where recordid in
(
select top 1 recordid
from @TestData MyData
where mydata.STUDENT_ ID = data.STUDENT_ID
order by TF desc
)

[Ginny28]

Thanks

Thanks for the info. I still struggled because it appears Top 1 only returns one row. I ended up with the following and I think it is what I need. Unless you can think of a better way:

select DISTINCT date_onset_symp t as dt1, description
from patient_diagnos is
where date_onset_symp t = (select max(pr.date_ons et_sympt)
from patient p,
patient_diagnos is pr
where pr.description = patient_diagnos is.description
and p.med_rec_nbr = $mrn
and pr.person_id = p.person_id
and pr.practice_id = $practice_id
and pr.enterprise_i d = $enterprise_id)

[ck9663]

Modifying Brad's code...

declare @TestData Table
(
STUDENT_ID VARCHAR(6),
TF Decimal(2,1)
)

insert into @TestData (student_id, tf)
select 'abc123', 0.2 UNION
select 'abc123', 0.7 UNION
select 'abc123', 0.4 UNION
select 'def123', 0.1 UNION
select 'def123', 0.5 UNION
select 'def123', 0.4

select * from @testdata

;with maxvalue
as
(
   select student_id, max(tf) as tf
   from @TestData MyData
   group by student_id   
)
select
   t.*
from @TestData t
   inner join maxvalue m on m.student_id = t.student_id and m.tf = t.tf

One catch: Watch out for duplicates...

Happy Coding!!!

~~ CK