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.
JeffMCCAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
D'OH, sorry, I forgot to add the critical WHERE condition:

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
WHERE row_num = 1
ORDER BY Tract_Code
0
 
Scott PletcherSenior DBACommented:
SELECT Tract_Code, Lease_Code, Expired_Date
FROM (
    SELECT PTL.*, PMT.*,
        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
0
 
JeffMCCAuthor Commented:
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

0
 
JeffMCCAuthor Commented:
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

0
 
JeffMCCAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.