Results 1 to 3 of 3

Thread: SQL Query Issue

  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: SQL Query Issue

    Hi,

    I have a property database that has a tenancy table, and i want to be able to extract the last tenants details at a each property, whether they are still there or not.

    The tenants are stored by a property reference (which is a unique id in the property table) and a tenant id, which increments each time a new tenant moves in to a property. So the combination of these two tells us which property the tenant stayed and what number tenant they were.

    Essentially all i'm looking for really is a way to query this table so that i only return the highest tenant id for each property reference. This will either be the current tenant if the property is tenanted or the last tenant if the property is empty.

    Could someone please assist with some sql query code for doing this?

    Thank you very much

    Liam

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by liam79
    ... only return the highest tenant id for each property reference.
    Code:
    select propertyid, max(tenantid) from tenancy group by propertyid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    2

    Thanks very much

    Thank you for your prompt response

Posting Permissions

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