Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2015
    Posts
    1

    Unanswered: Integrate query in query...

    Hi Guys,

    The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The last function finds the last record in that table, but i need to find the most recent record in the table according to a date field.

    Code:
    SELECT
            tblinmate.statusid,
            tblinmate.activedate,
            Last(tblclassificationhistory.classificationid) AS LastOfclassificationID,
            Last(tblsquadhistory.squadid) AS LastOfsquadID,
            tblperson.firstname,
            tblperson.middlename,
            tblperson.lastname,
            tblinmate.prisonnumber,
            tblinmate.droppeddate,
            tblinmate.personid,
            tblinmate.inmateid
    FROM tblsquad
    INNER JOIN (tblperson
    INNER JOIN ((tblinmate
    INNER JOIN (tblclassification
    INNER JOIN tblclassificationhistory
            ON tblclassification.classificationid =
            tblclassificationhistory.classificationid)
            ON tblinmate.inmateid =
            tblclassificationhistory.inmateid)
    INNER JOIN tblsquadhistory
            ON tblinmate.inmateid =
            tblsquadhistory.inmateid)
            ON tblperson.personid = tblinmate.personid)
            ON tblsquad.squadid = tblsquadhistory.squadid
    GROUP BY tblinmate.statusid,
             tblinmate.activedate,
             tblperson.firstname,
             tblperson.middlename,
             tblperson.lastname,
             tblinmate.prisonnumber,
             tblinmate.droppeddate,
             tblinmate.personid,
             tblinmate.inmateid;
    The query below finds the most recent record in a table according to a date field, my problem is i dont know how to integrate this Query into the above to replace the "Last" function

    Code:
    SELECT a.inmateID,
           a.classificationID,
           b.max_date
    FROM (
        SELECT tblClassificationHistory.inmateID, 
               tblClassificationHistory.classificationID,                
               tblClassificationHistory.reclassificationDate
        FROM tblinmate
        INNER JOIN tblClassificationHistory
        ON tblinmate.inmateID = tblClassificationHistory.inmateID 
    ) a
    INNER JOIN (
        SELECT tblClassificationHistory.inmateID, 
               MAX(tblClassificationHistory.reclassificationDate) as max_date
        FROM tblinmate
        INNER JOIN tblClassificationHistory
        ON tblinmate.inmateID = tblClassificationHistory.inmateID
        GROUP BY tblClassificationHistory.inmateID  
    ) b
    ON a.inmateID = b.inmateID
    AND a.reclassificationDate = b.max_date
    ORDER BY a.inmateID;

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm pretty sure that you are not using Microsoft SQL Server.

    I'm going to guess that using the Max() function to replace the Last() function will do what you've described, but it depends on which database engine you're using.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •