469,138 Members | 1,394 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

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

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_code_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_code_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_code_id,
pr.description,
pr.date_onset_sympt as dt1,
pr.date_diagnosed as dt2,
pr.date_resolved as dt3,
pe.enc_timestamp as dt4
from patient p,
patient_diagnosis pr,
patient_encounter pe
where p.med_rec_nbr = $mrn
and pr.person_id = p.person_id
and pr.practice_id = $practice_id
and pr.enterprise_id = $enterprise_id
and pe.enc_id = pr.enc_id
order by pr.description, pe.enc_timestamp DESC

Thanks! I am new to this and really struggle once I get out of the simple select stuff!
Jun 30 '10 #1
3 1918
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
)
Jun 30 '10 #2
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_sympt as dt1, description
from patient_diagnosis
where date_onset_sympt = (select max(pr.date_onset_sympt)
from patient p,
patient_diagnosis pr
where pr.description = patient_diagnosis.description
and p.med_rec_nbr = $mrn
and pr.person_id = p.person_id
and pr.practice_id = $practice_id
and pr.enterprise_id = $enterprise_id)
Jun 30 '10 #3
ck9663
2,878 Expert 2GB
Modifying Brad's code...

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare @TestData Table
  3. (
  4. STUDENT_ID VARCHAR(6),
  5. TF Decimal(2,1)
  6. )
  7.  
  8. insert into @TestData (student_id, tf)
  9. select 'abc123', 0.2 UNION
  10. select 'abc123', 0.7 UNION
  11. select 'abc123', 0.4 UNION
  12. select 'def123', 0.1 UNION
  13. select 'def123', 0.5 UNION
  14. select 'def123', 0.4
  15.  
  16. select * from @testdata
  17.  
  18. ;with maxvalue
  19. as
  20. (
  21.    select student_id, max(tf) as tf
  22.    from @TestData MyData
  23.    group by student_id   
  24. )
  25. select
  26.    t.*
  27. from @TestData t
  28.    inner join maxvalue m on m.student_id = t.student_id and m.tf = t.tf
  29.  
  30.  
One catch: Watch out for duplicates...

Happy Coding!!!

~~ CK
Jun 30 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by HandersonVA | last post: by
3 posts views Thread by | last post: by
3 posts views Thread by William Wisnieski | last post: by
3 posts views Thread by Steve | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.