DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Using SQL to obtain the most recent record

  1. #1
    Rich Guest

    Using SQL to obtain the most recent record


    I've got a table containing multiple Testing records for the same individual.
    It contains a code identifying the person a test date and a status code.
    Basically I need to obtain the most recent record using that date and extracting
    the status code. What I need to do is stick it in another table for demographic
    purposes. I cant use the Order By clause in any of the update or insert statements.
    I cant use the MAX or MIN functions, I know in MS Access the SQL had First
    and Last functions but TSQL doesnt.

    Does any one have any ideas?

    Thanks

  2. #2
    Vadim Levinzon Guest

    Re: Using SQL to obtain the most recent record


    Insert into tblA
    select fldA,fldX....... from tblB T1 where fldD=(select MAX(fldD) from tblB
    T2 )

    just make sure select fileds match insert fileds



  3. #3
    BvR Guest

    Re: Using SQL to obtain the most recent record


    Use TOP (Select TOP 1 FROM .......)

    "Rich" <rich@granite.k12.ut.us> wrote:
    >


    >I've got a table containing multiple Testing records for the same individual.
    >It contains a code identifying the person a test date and a status code.
    >Basically I need to obtain the most recent record using that date and extracting
    >the status code. What I need to do is stick it in another table for demographic
    >purposes. I cant use the Order By clause in any of the update or insert

    statements.
    >I cant use the MAX or MIN functions, I know in MS Access the SQL had First
    >and Last functions but TSQL doesnt.
    >
    >Does any one have any ideas?
    >
    >Thanks



  4. #4
    Michael Levy Guest

    Re: Using SQL to obtain the most recent record

    This query should return the latest record for each person in the table.

    SELECT person, testdate, status
    FROM table
    WHERE testdate IN (
    SELECT MAX(testdate)
    FROM table a
    WHERE a.person = table.person AND testdate < GETDATE())

    -Mike
    --
    Michael Levy MCSD, MCDBA, MCT
    Consultant
    GA Sullivan
    michaell@gasullivan.com




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center