Trying to select a specific record

[ashab02]

Hello
I have table which has address changes recorded within it. It has aunique identifying field 'Key' and also has a foreign key 'FKEY'. The table currently looks like this:

Key FKEY DateOfChange Add1 Add2
1 100 01/01/2009 1 Test Street TestTown
2 100 02/01/2009 1 Test Street TestTown
3 100 03/01/2009 3 Test Street TestTown
4 100 04/01/2009 3 Test Street TestTown

So what I need is row 3 'DateOfChange' as the address has changed but I also need to take into account that the address hasnt changed since then.
Overall I need to get the date of the last address change and in this example its 03/01/2009.
Can someone point me in the right direction as to how to do this?
Thanks

 

[PWise]

Select fkey max(DateOfChange )
from Tablename
group by fkey

 

[ashab02]

Hello
Thanks for your reply but the max date of change would give me 04/01/2009 but I want 03/01/2009
Can you think of a away of doing that?

 

[gmmastros]

Try this.

Select  FKey, Max(MinDate)
From    (
        Select  T.FKey, Min(DateOfChange) As MinDate
        From    TableName As T
        Group By T.FKEy, Add1
        ) As AliasName
Group By FKey

Let me know if this works, and if you would like me to explain it.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom