asked on

I would like help with a MAX Query.

I need to return the Lease_Code with the newest date for each Tract_Code. There can be multiple Tracts with the same Lease_Code. I can get the newest date for each tract, but I am stuck trying to add the Lease_Code in those results.

View1 : PR_Mapping_Tract_TRS_Location_Detail
Tract_Code (not unique)
Lease_Code (used to link to Code in Pr_Table_Lease)

View2 : PR_Table_Lease.Code
Code (unique)
Expired_Date

This code returns the newest date for each tract code:

SELECT
  PR_Mapping_Tract_TRS_Location_Detail.Tract_Code,
  Max(PR_Table_Lease.Expired_Date) AS Max_Expired_Date
FROM
  PR_Table_Lease
  INNER JOIN PR_Mapping_Tract_TRS_Location_Detail
    ON PR_Table_Lease.Code = PR_Mapping_Tract_TRS_Location_Detail.Lease_Code
GROUP BY
  PR_Mapping_Tract_TRS_Location_Detail.Tract_Code

Open in new window

I am stuck adding Lease_Code in to the results.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JeffMCC

ASKER

When running that I got a message about column "Active" is used multiple time. Both tables contain fields named "Active". I changed PTL.*, PMT.* to only the fields I needed. I then got a message the Lease_Code was not a valid column. I changed it to Code. When I run, I get all records. My changed code is below:

Thanks

  SELECT Tract_Code, Code, Expired_Date
FROM (
    SELECT PTL.Code, PTL.Expired_Date, PMT.Tract_Code, 
        ROW_NUMBER() OVER(PARTITION BY PMT.Lease_Code ORDER BY PTL.Expired_Date DESC) AS row_num
    FROM
      PR_Table_Lease PTL
      INNER JOIN PR_Mapping_Tract_TRS_Location_Detail PMT
        ON PTL.Code = PMT.Lease_Code
) AS query1
ORDER BY Tract_Code

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JeffMCC

ASKER

You got me looking the the right direction.... I think I have it.... I added "WHERE row_num = 1". It seems to work. I'm doing some more testing...

SELECT Tract_Code, Code, Expired_Date
FROM (
    SELECT PTL.Code, PTL.Expired_Date, PMT.Tract_Code, 
        ROW_NUMBER() OVER(PARTITION BY PMT.Tract_Code ORDER BY PTL.Expired_Date DESC) AS row_num
    FROM
      PR_Table_Lease PTL
      INNER JOIN PR_Mapping_Tract_TRS_Location_Detail PMT
        ON PTL.Code = PMT.Lease_Code
        
) AS query1
where row_num = 1
ORDER BY Tract_Code

Open in new window

JeffMCC

ASKER

Worked great in test query. My ultimate goal was to create a view on SQL server. I had to remove the final ORDER BY line to get that to work, but after that it worked great. Very quick response.