Picking Row with earliest date for each person 1

[NikkieG]

Hi,

Sorry if this is a really simple question, but it's been driving me mad all morning.

I have a table where the data is in 4 columns: Person ID, Appointment Date, Postcode & Estate in which that postcode lies.

I have several rows for each person because the postcode & estate change from that person moves house.

From this, I need to know just the person ID and the Estate at the time of each persons' first appointment, e.g.

If the table shows:

ID[tab][tab][tab][tab][tab]AppDate[tab][tab][tab][tab]Estate
4001182238[tab][tab]2004-12-29[tab][tab]Toytown
4001182238[tab][tab]2006-02-02[tab][tab]Newtown

I would just want the following returned:

ID[tab][tab][tab][tab][tab]Estate
4001182238[tab][tab]Toytown

Is this possible without resorting to putting the data in to Excel?

I have tried using the min function on the appointment date but this isn't working because of having to group the data, i.e. I get a separate line for each estate each person has lived in.

Any help or advice very much appreciated.


Nik

 

[gmmastros]

>> Is this possible without resorting to putting the data in to Excel?

Yes. Of course this is possible.

Select [!]TableName[/!].*
From   [!]TableName[/!]
       Inner Join (
         Select Id, Min(AppDate) As MinDate
         From   [!]TableName[/!]
         Group By Id
         ) as AliasName
         On  [!]TableName[/!].Id = AliasName.Id
         And [!]TableName[/!].AppDate = AliasName.MinDate

Look ma! No Excel!



-George

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

 

[NikkieG]

Loving the shocked icon - I really didn't want to resort to Excel but my manager was sure I would have to!

You are an absolute star - thank you so much.

 

[onpnt]

>>my manager was sure I would have to

That's why he or she is a manager and not doing the work

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[fikir]

>> That's why he or she is a manager and not doing the work

Nice!!