Compare records

I have columns with date, time, value. I want to find the last date/time and display it's corresponding value.

Who is Participating?
here goes:

Above "FROM" Addd this line:

(SELECT s.NA FROM dbo.ITX_vw_tbl_Chemistry s WHERE s.DateTime = max(c.DateTime) and s.patientid = c.patientid) maxts

Open in new window

edit: added patient ID to subselect where, just in case you need it.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you may want to read this article:

the technique I would suggest is this:
 select value
   from (select value, row_number() over ( order by date desc, time desc ) rn from yourtable )
 where rn = 1

Open in new window

LCNWAuthor Commented:
I just need the MAX(DateTime) but then grab the corresponding value field.
LCNWAuthor Commented:
Here's the query:

SELECT     TOP (100) PERCENT p.UnosId AS 'UNOS ID', ro.Name AS Hospital, dr.CrossClampedOn AS CrossClamp,
                      MAX(CASE WHEN evCriteria.parentValueId = '860f6e6d-26e5-4667-8cb2-774f4dc249ae' THEN evCriteria.Description ELSE '0' END) AS 'Donor Criteria Type',
                      CONVERT(varchar(10), p.Age) + '/ ' + p.AgeUnits AS 'Age/Units', p.Sex,
                      MAX(CASE WHEN ev.EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0' THEN ev.Description ELSE '0' END) AS 'Race', CONVERT(varchar(10), p.Height)
                      + '/' + p.HeightUnits AS 'Height/Units', CONVERT(varchar(10), p.Weight) + '/' + p.WeightUnits AS 'Weight/Units', MAX(CASE WHEN c.NA IS NOT NULL
                      THEN CONVERT(nvarchar(10), c.NA) END) AS 'Sodium Peak'
FROM         dbo.ITX_vw_tbl_Referral AS r INNER JOIN
                      dbo.ITX_vw_tbl_DonorReferral AS dr ON r.Id = dr.ReferralId INNER JOIN
                      dbo.ITX_vw_tbl_Patient AS p ON r.PatientId = p.Id INNER JOIN
                      dbo.ITX_vw_tbl_Organization AS ro ON r.ReferringOrganizationId = ro.Id INNER JOIN
                      dbo.ITX_vw_tbl_Chemistry AS c ON p.PatientId = c.PatientId LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS ev ON ev.Value = CONVERT(nvarchar(50), p.Race) LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS evCriteria ON evCriteria.Id = dr.OrganOutcomeDetail
GROUP BY p.UnosId, ro.Name, dr.CrossClampedOn, p.Age, p.Sex, p.Height, p.HeightUnits, p.Weight, p.WeightUnits, p.AgeUnits
HAVING      (p.UnosId = 'VHO228')
ORDER BY Hospital

I get the MAX(c.NA), but I also want the last c.NA and they have c.DateTime for timestamps.
Am a bit dizzy and can't wade through your SQL, but if you don't want to do partitioning, why can't you simply add a subselect in the select clause along the lines of

(select s.value from table s where = max(

 where "o" is the alias of the outside table and table is the same table?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.