Query to bring top latest record

  • Hi Experts,

    can you confirm the query to pull out only one latest record for each name descending with date so in below case i just want to pull Harry record of date 2009-01-05 and Robert record of 2009-01-03?

    Name Date

    Harry2009-01-05

    Harry2009-01-02

    Harry2009-01-01

    Robert2009-01-03

    Robert2009-01-01

  • sqlqueue (4/14/2009)


    Hi Experts,

    can you confirm the query to pull out only one latest record for each name descending with date so in below case i just want to pull Harry record of date 2009-01-05 and Robert record of 2009-01-03?

    Name Date

    Harry2009-01-05

    Harry2009-01-02

    Harry2009-01-01

    Robert2009-01-03

    Robert2009-01-01

    Select Name, Max(date) as Date

    From TableA

    Group By Name

    Order by Name Desc

    [/Code]

    That should get you what you need unless I am completely misunderstanding what you are trying to accomplish above.

    Fraggle

  • In addition to fraggle's solution, in case if you need the latest date inserted with respect to name considering the dates could be random instead of descending then the following query might help you.

    CREATE TABLE #TEMP(UID INT IDENTITY ,NAME VARCHAR(10),MYDATE DATETIME)

    INSERT INTO #TEMP SELECT 'Harry','2009-01-05'

    INSERT INTO #TEMP SELECT 'Harry','2009-01-02'

    INSERT INTO #TEMP SELECT 'Harry','2009-01-15'

    INSERT INTO #TEMP SELECT 'Robert','2009-01-03'

    INSERT INTO #TEMP SELECT 'Robert','2009-01-01'

    SELECT * FROM #TEMP

    SELECT T.* FROM #TEMP T

    INNER JOIN(SELECT MAX(UID) UID, NAME FROM #TEMP GROUP BY NAME) A ON A.UID = T.UID

    DROP TABLE #TEMP

    Thanks

    "I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

  • jchandramouli (4/14/2009)


    In addition to fraggle's solution, in case if you need the latest date inserted with respect to name considering the dates could be random instead of descending then the following query might help you.

    CREATE TABLE #TEMP(UID INT IDENTITY ,NAME VARCHAR(10),MYDATE DATETIME)

    INSERT INTO #TEMP SELECT 'Harry','2009-01-05'

    INSERT INTO #TEMP SELECT 'Harry','2009-01-02'

    INSERT INTO #TEMP SELECT 'Harry','2009-01-15'

    INSERT INTO #TEMP SELECT 'Robert','2009-01-03'

    INSERT INTO #TEMP SELECT 'Robert','2009-01-01'

    SELECT * FROM #TEMP

    SELECT T.* FROM #TEMP T

    INNER JOIN(SELECT MAX(UID) UID, NAME FROM #TEMP GROUP BY NAME) A ON A.UID = T.UID

    DROP TABLE #TEMP

    Thanks

    Hi Chandramouli,

    How your statement works with the original posting?

    -- your statement

    SELECT T.* FROM #TEMP T

    INNER JOIN(SELECT MAX(UID) UID, NAME FROM #TEMP GROUP BY NAME) A ON A.UID = T.UID

    RESULT

    UIDNAMEMYDATE

    3Harry2009-01-15 00:00:00.000

    5Robert2009-01-01 00:00:00.000

    ---Fraggle statement

    Select Name, Max(MYDATE) as Date

    From #TEMP

    Group By Name

    Order by Name Desc

    RESULT

    NameDate

    Robert2009-01-03 00:00:00.000

    Harry2009-01-15 00:00:00.000

    ARUN SAS

  • There are basically three ways you can do it; MAX, TOP, and ROW_NUMBER. They all work. They all don't work equally well. I wrote an article, with code examples, comparing the three. It's available here, the article called, The Questions.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I have a similar problem where I have records of individuals that are being tracked as they move through an area. I only what the see the last place that they have been, but I'm using a smalldate datatype. Will this still work?

  • peggyah (4/22/2009)


    I have a similar problem where I have records of individuals that are being tracked as they move through an area. I only what the see the last place that they have been, but I'm using a smalldate datatype. Will this still work?

    Start a new thread, peggyah. Can you post a table create statement with some inserts to populate the table with sample data? This will help people to model your problem and provide you with a practical solution. The link in my sig will show you how to do this.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I tried the MAX function on my data table

    SELECT TOP 100 MAX(TriageTagNumber) AS [Triage Tag], Location, [Currect Condition], ArrivalTime

    FROM dbo.[Patient Location]

    GROUP BY ArrivalTime, [Currect Condition], Location

    ORDER BY MAX(TriageTagNumber)

    But I get all transactions instead of just the latest Arrival time for each TriageTagNumber

    Triage Tag Location Current Condition ArrivalTime

    001 Delayed 4/23/2009 7:48:54 AM

    001 MS5 Stable4/23/2009 7:53:01 AM

    002 ICU Critical4/23/2009 7:53:23 AM

    002 Immediate 4/23/2009 7:49:05 AM

    003 Delayed 4/23/2009 7:49:11 AM

    004 MS5 Guarded4/23/2009 7:53:40 AM

    005 OR Stable4/23/2009 7:53:57 AM

    005 Expectant/Deceased 4/23/2009 7:49:18 AM

    006 Minor/Walk Wounded 4/23/2009 7:49:25 AM

    006 Family Medicine Good4/23/2009 7:54:20 AM

    008 Family Medicine Good4/23/2009 7:54:46 AM

    008 Triage 4/23/2009 7:49:32 AM

    I just want to see (Please Help)

    Triage Tag Location Current Condition ArrivalTime

    001 MS5 Stable4/23/2009 7:53:01 AM

    002 ICU Critical4/23/2009 7:53:23 AM

    003 Delayed 4/23/2009 7:49:11 AM

    004 MS5 Guarded4/23/2009 7:53:40 AM

    005 OR Stable4/23/2009 7:53:57 AM

    006 Family Medicine Good4/23/2009 7:54:20 AM

    008 Family Medicine Good4/23/2009 7:54:46 AM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply