Compare records

Posted on 2012-08-24
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
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    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

    LVL 1

    Author Comment

    I just need the MAX(DateTime) but then grab the corresponding value field.
    LVL 1

    Author Comment

    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.
    LVL 2

    Assisted Solution

    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?
    LVL 2

    Accepted Solution

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Database restore 8 42
    T-SQL: Why is Syntax So Difficult 5 21
    SQL Database Mail Setup 1 19
    Software suggestion 12 18
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    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
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now