On Mon, Apr 28, 2008 at 6:30 AM, Mark Stanton <[EMAIL PROTECTED]> wrote:
It's really helpful if you provide some sample data and an example of
the result you want:
>
> I have a table of data entry and an audit table that records changes
> to the data.
CREATE TABLE DataEntry (iDEPK I, type c(10))
CREATE TABLE DEAudit (iDEAPK I, iDEFK I, tChanged T)
>
> They want all data of a certain type (status "open") and the date and
> username from the *latest* audit entry for each row.
What you'd like is "Top 1 for each group" which you'd think this might
give you but won't:
SELECT TOP 1 DataEntry.*, DEAudit.*
FROM DataEntry JOIN DEAudit o niDEPK = iDEFK
GROUP BY iDEPK
ORDER BY tChanged DESCENDING
>
> The only way I can think to do it is with a subselect in the join
> condition, but this doesn't look possible from the help and my
> experimenting.
The correlating subquery needs to find the TOP 1 for each Data Entry
PK. You can use TOP 1, but that aggregates and truncates the entire
result, or you can sort the result by the ordering field and use the
MAX() aggregation function:
SELECT DataEntry.*, DEAudit.*
FROM DataEntry JOIN DEAudit ON niDEPK = iDEFK
WHERE DEAudit.tChanged IN
(SELECT MAX(DEA2.tChanged) FROM DEAudit DEA2
WHERE DEA2.iDEFK = DataEntry.iDEPK)
It certainly is possible and probably better than my solution above,
which would require aggregating functions on each of the fields in the
child/audit table.
>
> Anyone?
>
> Yes, it's all in native VFP (8.0).
>
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
