If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Group By or MAX Question
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. |
#2
|
|||
|
|||
Group By or MAX Question
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 "Toria" wrote in message ... 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. |
#3
|
|||
|
|||
Group By or MAX Question
On Wed, 12 May 2010 12:48:01 -0700, Toria
wrote: 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? -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Group By or MAX Question
Toria wrote:
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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|