Pull the most current record

[thorny00]

I need the last eligibility record for each person from the table foo

I need the following fields first,last,address1,address2,city,state,zip,filler.

I am using a field YMDCreated to determine the last record.

How is this done?

Thanks for you help and time, it's greatly appreciated!

 

[EdwinGene]

You don't give much detail about the structure of table foo, so I'm going to make a few assumptions.

1. A person can have more than one record in table foo. Otherwise why would you need find a way to select the most recent?

2. There must be a way, in table foo, to determine which records are associated with a certain person. I will assume there is a non-unique column in table foo which assigns a code unique to an individual person to all records in table foo which are associated with that person. I'll call this column, "personcode".



select
f1.first,
f1.last,
f1.address1,
f1.address2,
f1.city,
f1.state,
f1.zip,
f1.filler
from
foo f1
where
f1.YMDCreated =
(
Select
Max(YMDCreated)
From
foo f2
Where
f2.personcode = f1.personcode
)

 

[thorny00]

Thanks EDWINGENE after my 2nd cup of coffee, I figured it out. This is what I came up with.

SELECT
FIRSTNAME,
LASTNAME,
ADDRESS1,
ADDRESS2,
CITY,
STATE,
ZIP,
FILLER, YMDCREATED
FROM FOO R
WHERE YMDCREATED=(SELECT MAX(YMDCREATED) FROM FOO
WHERE LASTNAME = R.LASTNAME AND FIRSTNAME = R.FIRSTNAME)
ORDER BY LASTNAME, FIRSTNAME

 

[jbenson001]

Why use a sub select when a simple group by would work

SELECT
FIRSTNAME,
LASTNAME,
ADDRESS1,
ADDRESS2,
CITY,
STATE,
ZIP,
FILLER, Max(YMDCREATED)
FROM FOO R
group by firstname,lastname,address1,addres2... ryv.

 

[EdwinGene]

jbenson001,

Should work, unless, for some reason the same person has multiple address values (for example, my zip code was changed last year even though I still lived at the same address). Then you would return multiple records for that person. A rare occurrence, I admit, but a possibility, and one that should be accounted for, don't you think?

Also, you can't just group by first and last name. How many John Smith's do think there are in this country?

 

[EdwinGene]

thorny00,

Using Last Name and First Name for your unique identifiers may not work. There are probably millions of John Smith's and Jim Jones's in the country. It may not be happening in your DB right now, but it could in the future. It's something that needs to be accounted for in your code. That's why having a separate column that REALLY uniquely identifies a person is preferable to using Last and First Name.