Results 1 to 5 of 5

Thread: [RESOLVED] Need help with SQL Query

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2011
    Posts
    499

    Resolved [RESOLVED] Need help with SQL Query

    I have 2 tables tblJob & tblJobSession

    https://www.dropbox.com/scl/fi/k36y5...z8g3luz94&dl=0

    I would like to show all Jobs with open jobsessions , but only the last visit

    so out of the table tblJobSession i would like to only show RecID 2 and 3

    data to return would be

    tblJob.JobID , tblJob.Reg , tblJob.JobType , tblJobSession.VisitDate , tblJobSession.Engineer , tblJobSession.Status

    can some one help. I thought i had it with

    SELECT tblJobSession.VisitNo, tblJobSession.JobID, tblJobSession.VisitDate, tblJobSession.Engineer, tblJobSession.Status, tblJob.Reg, tblJob.Status
    FROM tblJob INNER JOIN tblJobSession ON tblJob.JobID = tblJobSession.JobID
    WHERE (((tblJobSession.VisitNo)=(select max(tblJobSession.visitNo) from tblJobSession)));

    but this does not show the 2 records i want

    tks.

  2. #2
    New Member
    Join Date
    Dec 2020
    Posts
    5

    Re: Need help with SQL Query

    The attached link should point you in the right direction on how to modify your Query:
    Just substitute VisitNo for Date

    https://sqlbenjamin.wordpress.com/20...ecord-by-date/

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2011
    Posts
    499

    Re: Need help with SQL Query

    tks for the link

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,438

    Re: [RESOLVED] Need help with SQL Query

    Untested
    Should work with any DBMS which understands CTE's and ROW_NUMBER-Function (SQLite, MySQL8 --> NOT MySQL5.X)

    Code:
    WITH
        CTE AS (SELECT JobID, VisitNo, VisitDate, Engineer, Status, 
            ROW_NUMBER() OVER(PARTITION BY JobID ORDER BY VisitNo DESC) AS RN 
            FROM tblJobSession)
    
    SELECT 
        CTE.VisitNo, 
        CTE.JobID, 
        CTE.VisitDate, 
        CTE.Engineer, 
        CTE.Status As SessionStatus, 
        TJ.Reg, 
        TJ.Status As JobStatus
    FROM tblJob AS TJ
    INNER JOIN CTE ON TJ.JobID=CTE.JobID AND CTE.RN=1
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,438

    Re: [RESOLVED] Need help with SQL Query

    Without ROW_NUMBER and WITH-Claus

    Untested
    Code:
    SELECT 
        CTE.VisitNo, 
        CTE.JobID, 
        CTE.VisitDate, 
        CTE.Engineer, 
        CTE.Status As SessionStatus, 
        TJ.Reg, 
        TJ.Status As JobStatus
    FROM tblJob AS TJ
    INNER JOIN 
        (SELECT TJS.JobID, TJS.VisitNo, TJS.VisitDate, TJS.Engineer, TJS.Status
        FROM tblJobSession As TJS 
        INNER JOIN 
            (SELECT JobID, Max(VisitNo) As MaxVisitNo FROM tblJobSession GROUP BY JobID) AS T1 
        ON T1.JobID=TJS.JobID AND T1.MaxVisitNo=TJS.VisitNo) AS CTE
    ON TJ.JobID=CTE.JobID
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width