Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Compare records

Posted on 2012-08-24
Medium Priority
Last Modified: 2012-08-24
I have columns with date, time, value. I want to find the last date/time and display it's corresponding value.

Question by:LCNW
  • 2
  • 2
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 38331183
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


Author Comment

ID: 38331208
I just need the MAX(DateTime) but then grab the corresponding value field.

Author Comment

ID: 38331222
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.

Assisted Solution

Baleboos earned 1600 total points
ID: 38331249
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 s.date = max(o.date))

 where "o" is the alias of the outside table and table is the same table?

Accepted Solution

Baleboos earned 1600 total points
ID: 38331258
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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question