Group By or MAX Question

[Toria]

I have a root ID field. I have a close date field. There can be more than one
record with the same root ID. I need to pull only one of them with the latest
close date.

Thank you.

 

[vanderghast]

Many possible solutions, the easiest one is probably to do it in two
queries:

SELECT id, max(closeDate) AS mdate
FROM tableNameHere
GROUP BY id

saved as q1. Then:

SELECT a.*
FROM tableNameHere AS a INNER JOIN q1
ON a.id=b.id AND a.closeDate = q1.mdate



Other solutions at http://www.mvps.org/access/queries/qry0020.htm



Vanderghast, Access MVP

 

[John W. Vinson]

I have a root ID field. I have a close date field. There can be more than one
record with the same root ID. I need to pull only one of them with the latest
close date.

Thank you.

A Subquery will do this for you; use a criterion on the close date field of

=(SELECT Max(X.[Close date] FROM yourtable AS X WHERE X.[Root ID] =
yourtable.[Root ID])

If there are two records with the same close date you'll get both. Does your
table have a Primary Key?

 

[Marshall Barton]

I have a root ID field. I have a close date field. There can be more than one
record with the same root ID. I need to pull only one of them with the latest
close date.

If you want the records in the table with the latest close
date across the entire table:

SELECT TOP 1 table.*
FROM table
ORDER BY [close date field] DESC

If you want the data in the records with latest close date
for each group id:

SELECT table.*
FROM table
WHERE table.[close date field] =
(SELECT Max(X.[close date field])
FROM table As X
WHERE X.[root id] = table.[root id])

or, probably faster:

SELECT table.*
FROM table INNER JOIN
(SELECT X.[root id],
Max(X.[close date field]) As Latest
FROM table As X
GROUP BY X.[root id]) As M
ON M.[close date field] = table.[close date field]
And M.[root id] = table.[root id])

Note that if there are multiple records with the same close
date (for a single group id), the query will return all thos
records.