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?