Select most recently edited item AND type if another doesn't exist

[Soteriologist]

I've got a big problem that I'm trying to figure out:

I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.

I don't need this for an individual ID select, I need it applied to every record from the table.

My address table has some columns that look like:
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]

LocationIDs are foreign IDs of Companies from another table.

How can I do this efficiently with perfomance in mind???

Thank you in advance for any and all replies...

 

[Soteriologist]

Also, there is an [AddressID] [int] column

AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID

 

[vbkris]

some sample data will be helpfull...

Known is handfull, Unknown is worldfull

 

[Soteriologist]

Here's what a few rows look like:

[AddressID] [LocationID] [Type] [Edit Date]
1 11111 Mail 4/29/2006 3:09:24 PM

2 44444 Physical 7/2/2007 6:10:03 PM

3 11111 Physical 9/3/2007 7:05:20 PM

4 22222 Mail 4/29/2006 3:09:24 PM

5 22222 Mail 9/9/2007 11:0:00 AM



and what I want to get returned would be:

[AddressID] [LocationID] [Type] [Edit Date]

1 11111 Mail 4/29/2006 3:09:24 PM

2 44444 Physical 7/2/2007 6:10:03 PM

5 22222 Mail 9/9/2007 11:0:00 AM

 

[gmmastros]

Try this...

Select TableName.*
From   TableName
       Inner Join (
         Select LocationId,
                Max([Edit Date]) As MaxEditDate
         From   TableName
         Group BY LocationId
         ) As A
         On TableName.LocationId = A.LocationId
         And TableName.[Edit Date] = A.MaxEditDate

If this works for you, and you would like further explanation, let me know.


-George

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