473,889 Members | 1,563 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

2 New Member
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!
Jun 30 '10 #1
3 2175
Brad Orders
21 New Member
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
Ginny28
2 New Member
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)
Jun 30 '10 #3
ck9663
2,878 Recognized Expert Specialist
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

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

Similar topics

6
4478
by: HandersonVA | last post by:
There are several day_timestamp for each index_id. Anyone can help me to write a sql to generate the most recent day_timestamp of index_ids which has not accessed into the system in 90 days from today's date. So, I need to get the most recent date and time for each index_id in 90 days from today's date. Sample data: Index_id day_timestamp
3
2308
by: | last post by:
I have a collumn filled with dates. How would I go about selecting the record with the most recent date. using access 2000. thanks!
3
1769
by: William Wisnieski | last post by:
Hello Everyone, I'm helping out a non-profit school with their database. They would like to know the last gift made by each donor, the donor name, and the gift amount. I built a query based on two tables. The donor name is from the first table . The gift and are in the second table . The query works except for one problem. It will return the proper number of records with the most recent gift date as long as I don't add the
1
1654
by: Jeroen | last post by:
Hello, Is it possible to retrieve the date when a record is created in an Access database table? And if so, how can I do that (by code)? Thanks in advance, Jeroen Elias
2
6115
by: Jeroen Elias | last post by:
Hello, Is it possible to retrieve the date when a record is created in an Access database table? And if so, how can I do that? Thanks in advance, Jeroen Elias
2
4841
by: gafchic | last post by:
I manage a training database where I work and I would like to run a make table query. The table I want to query has a list of trainings our employees have taken and the dates they have taken the trainings. The table only lists the employee number and it is related to a table which includes the employees' names and the employees' numbers. I would like my query to look at each employee and find all the records where they have taken, for...
8
14321
by: Jason H | last post by:
Hi, I am sure I am just overlooking the obvious, but I am having a little trouble with this one... I am setting up an inventory database that tracks company tools and their location. This database has a table tblToolInfo and another table tblTransferHistory which are linked by a Tool ID field. As tools are checked out they are recorded in tbl TransferHistory based on ID, location taken from, location taken to, authorizing person, and date....
1
1678
by: Proaccesspro | last post by:
I have 2 tables in a query. One of the tables contains a field titled Action Date. How can I query for the most recent date in the field Action Date? Dmax?
5
1959
by: giandeo | last post by:
Hello Experts. I am unable to retrieve records base on a date. Could you please help me. Here is my code for user to insert a date : <form name="hello" method="post" action="querytable.asp"> Please enter date (mm/dd/yyyy)<input type="text" name="adate">
3
1460
by: Steve | last post by:
I have a databse tracking container movements. I am happily recording each movement and the date of movement and can see where each container is by sorting by date of movement. Is there a way I can produce a query which would show, for each container, only the most recent move
0
9962
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9809
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10791
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7993
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7150
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5829
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4647
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3256
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.