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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.