Select Distinct - Most Recent

[djwatts]

Hello all,

I am doing a data migration from an odbc data source, so it's not my design. I have two tables (well about 200 in total). Called 'PERSON' and 'TENANCY' for simplicity

'PERSON' [PrimaryKey - 'PERSON_REF'] contains details on the tenant and inherently 'TENANCY' [PrimaryKey - 'TENANCY_REF'] contains details on that persons tenancies which there are often more than one per person. These are related by the field 'PERSON_REF'

I am trying to write a query to pull ONLY the most recent tenancy details (defined by the 'START_DATE' field) over per person.

I have tried using SELECT DISTINCT/DISTINCTROW does this select the first record that it finds sequentially?

In this case should I sort the query by the 'START_DATE' field and use the SELECT DISTINCT statement.

Or is there a way in SQL to compare the 'START_DATE' of two adjacent records and return the most recent one?

I want to avoid processing the data using VB after the query has been run if possible.

Thanks for any help on this guys it's much appreciated.

Regards

 

[PHV]

You may try something like this (SQL code):
SELECT P.*, T.*
FROM ((
SELECT PERSON_REF,Max(START_DATE) AS LastDate FROM TENANCY GROUP BY PERSON_REF
) AS L INNER JOIN PERSON AS P ON L.PERSON_REF = L.PERSON_REF)
INNER JOIN TENANCY AS T ON L.PERSON_REF = T.PERSON_REF AND L.LastDate = T.START_DATE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mp9]

Try this:

SELECT Person.Person_Ref, Tenancy.Tenancy_Ref, Max(Tenancy.Start_Date) AS MaxOfStart_Date
FROM Person INNER JOIN Tenancy ON Person.Person_Ref = Tenancy.Person_Ref GROUP BY Person.Person_Ref, Tenancy.Tenancy_Ref;

http://www22.brinkster.com/accessory/