In the following query, the Child table can hold one or more records for an individual
with the same ssn value but a different intake_date value. Is there a way to ensure that
the selected records always have the most recent intake_date value?

SELECT MAX(child_id) as child_id, MAX(race) as race, MAX(hispanic) as hispanic,
MAX(client_age) as client_age INTO MEMORY\Temp FROM Child
GROUP BY ssn;

The results are then used in a subsequent SELECT to count individual totals.

Mark Shapiro

Mark,

<< In the following query, the Child table can hold one or more records for
an individual with the same ssn value but a different intake_date value. Is
there a way to ensure that the selected records always have the most recent
intake_date value? >>

You're missing the SSN column in the query, for starters (DBISAM requires
that the GROUP BY columns exist in the SELECT column list).  The desired
query would look like this:

SELECT ssn, MAX(instake_date) AS intake_date, MAX(child_id) as child_id,
MAX(race) as race, MAX(hispanic) as hispanic,
MAX(client_age) as client_age INTO MEMORY\Temp FROM Child
GROUP BY ssn;

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim. Should I assume by the lack of responses that there is no way to group by SSN
and ensure that the record with the most recent intake_date value is selected?

Mark Shapiro

"Mark Shapiro" <infocus@swbell.net> wrote in message
news:C1171A5E-6211-432F-98BF-B8FDC6112F46@news.elevatesoft.com...
> In the following query, the Child table can hold one or more records for
an individual
> with the same ssn value but a different intake_date value. Is there a way
to ensure that
> the selected records always have the most recent intake_date value?
>
> SELECT MAX(child_id) as child_id, MAX(race) as race, MAX(hispanic) as
hispanic,
> MAX(client_age) as client_age INTO MEMORY\Temp FROM Child
> GROUP BY ssn;
>
> The results are then used in a subsequent SELECT to count individual
totals.
>

This is a very odd query. Why MAX(race), for example? One assumes that each
individual will be of one race. MAX(age)?

Anyway, in answer to your question, you need to select ssn if you want to
GROUP BY ssn, and then select max(intake_date). But I think we are not
communicating, it would be better if you explain in more detail what you are
trying to accomplish.

Robert

Mark,

<< Thanks Tim. Should I assume by the lack of responses that there is no way
to group by SSN and ensure that the record with the most recent intake_date
value is selected? >>

Umm, does my response count ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Okay, let me try this a different way.

Query 1

SELECT child_id, race, hispanic, client_age, ssn, intake_date FROM Child
GROUP BY ssn

This query displays one record for each unique ssn value but does not necessarily display
the record with the most recent intake_date.

Query 2

SELECT child_id, race, hispanic, client_age, ssn, intake_date FROM Child
GROUP BY ssn, intake_date

This query will display multiple records for clients with the same ssn but different
intake_date values.

Is there a way to group the table in such a way that the result set always consists of
unique ssn values and also the most recent intake_date for that ssn?

Mark Shapiro

"Mark Shapiro" <infocus@swbell.net> wrote in message
news:8F4BA578-C93F-45FF-9B1D-F56D4D4D603B@news.elevatesoft.com...
> Okay, let me try this a different way.
>
> Query 1
>
> SELECT child_id, race, hispanic, client_age, ssn, intake_date FROM Child
> GROUP BY ssn
>
> This query displays one record for each unique ssn value but does not
necessarily display
> the record with the most recent intake_date.
>
> Query 2
>
> SELECT child_id, race, hispanic, client_age, ssn, intake_date FROM Child
> GROUP BY ssn, intake_date
>
> This query will display multiple records for clients with the same ssn but
different
> intake_date values.
>
> Is there a way to group the table in such a way that the result set always
consists of
> unique ssn values and also the most recent intake_date for that ssn?
>

Should have said that before.

select ssn tssn, max(intake_date) thidate into memory\temp from child group
by ssn;
select yourfields from child
join memory\temp  on (ssn = tssn) and (thidate = intake_date);

You will get "yourfields" all from the same record, the one that matches the
memory table on both ssn and intake_date. Of course, if you have more than
one identical intake_date with the same ssn, you will get multiple records
extracted, and this logic does not work.

Robert