Minimum Date with group by

[Alex McDevitt]

I wondered if anyone new how to return the following

Table of Data

ID|ID2|Date
----------------
1 | 1 | 20/01/05
2 | 1 | 21/01/05
3 | 1 | 19/01/05
4 | 2 | 20/01/05
5 | 2 | 25/01/05

I want to return the minimum date grouped by ID2, but with the corresponding ID i.e.

ID|ID2|Date
----------------
3 | 1 | 19/01/05
4 | 2 | 20/01/05

cheers for your help...

 

[Advertiser]

Based on the fact that the ID is unique...

Do a totals query with ID2 as the 'group by', date as the MIN and ID as the MAX !!


sql = "SELECT ID2, Min(DateField) AS MinOfDate, Max(ID) AS MaxOfID
FROM tablename GROUP BY ID2"

George

 

[Alex McDevitt]

Cheers George,

The problem is it's not always the max ID, the returned results are...

ID1 | MinOfDate | MaxOfID
--------------------------
1 |19/01/05 | 3
2 |20/01/05 | 5

Not....

ID1 | MinOfDate | MaxOfID
--------------------------
1 |19/01/05 | 3
2 |20/01/05 | 4

SQL mixes the data....

 

[Jon K]

You can first create a Totals Query. Then in a second query link the table to the Totals Query to retrieve the records.

qryOne:-
SELECT [[TableName]].ID2, Min([[TableName]].Date) AS MinOfDate
FROM [[TableName]]
GROUP BY [[TableName]].ID2;

qryTwo:-
SELECT [TableName].ID2, [TableName].Date, [TableName].ID
FROM [TableName] INNER JOIN qryOne ON ([TableName].Date = qryOne.MinOfDate) AND ([TableName].ID2 = qryOne.ID2);


Run the second query.


Note
Date is the name of an Access function. It's better not to use it as a field name.
.

 

[Alex McDevitt]

Cheers...thats just what I just thought of...my only hessitation was the fact that the dates could be the same...so I had to do another query with min ID...don't things get complicated!

Thanks for everyones help..