Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    12

    Unanswered: SQL Query Problem using MAX or Top

    I am struggling with a query where I am trying to find the latest lease information for each unique resident. See data sample below:

    ResidentNum LeaseStart LeaseEnd
    1 1/1/2003 12/31/2003
    1 1/1/2004 6/30/2004 (this is a lease renewal)
    2 7/1/2004 12/31/2004
    3 1/1/2005 5/31/2005
    3 6/1/2005 12/31/2005 (so is this)
    4 1/1/2006 8/31/2006

    What I want is to only get the most current lease data back for each resident like this:

    ResidentNum LeaseStart LeaseEnd
    1 1/1/2004 6/30/2004 (this is a lease renewal)
    2 7/1/2004 12/31/2004
    3 6/1/2005 12/31/2005 (so is this)
    4 1/1/2006 8/31/2006

    I have tried using MAX and Top 1 etc with no luck. Any ideas would be much appreicated.

    Thanks, Mike

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a correlated subquery:
    Code:
    select ResidentNum 
         , LeaseStart 
         , LeaseEnd
      from daTable as T
     where LeaseStart =
         ( select max(LeaseStart)
             from daTable
            where ResidentNum = T.ResidentNum )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    12
    This is not quite right. It still gives me just 1 row. Other ideas?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest two tiny changes (both in red) to R937's code, to make the meaning more explicit:
    Code:
    select ResidentNum 
         , LeaseStart 
         , LeaseEnd
      from daTable as T
     where LeaseStart =
         ( select max(LeaseStart)
             from daTable AS z
            where z.ResidentNum = T.ResidentNum )
    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks pat, but i'll wager that wasn't the cause of the problem

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    select	ResidentNum,
    	LeaseStart,
    	LeaseEnd
    from	daTable
    	inner join
    		(select	ResidentNum,
    			max(LeaseStart) as LeaseStart
    		from	daTable
    		group by ResidentNum) LastRecords
    		on daTable.ResidentNum = LastRecords.ResidentNum
    		and daTable.LeaseStart = LastRecords.LeaseStart
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2004
    Posts
    12

    Got it working

    I had my where clause wrong. I appreciate everyone's help!

    Thanks, Mike

Posting Permissions

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