COUNT for MAX

[anselme]

for access 2000

with the following

Table1

Id1 | Id2 | Date
--------------------------
1 | 2 | 13/08/2004
1 | 2 | 01/06/2004
1 | 2 | 05/11/2003
1 | 2 | 10/06/2004

2 | 2 | 01/06/2004
2 | 2 | 20/02/2006
2 | 2 | 13/08/2004
2 | 2 | 10/06/2004
2 | 2 | 05/11/2003


I need Distinct Id1 and Id2 for MAX(Date)
1 | 2 | 13/08/2004
2 | 2 | 20/02/2006


thank you for helping

[nosepicker]

For the data you've provided, you can just do this:

SELECT id1, id2, CONVERT(varchar(10), MAX(CONVERT(datetime, date)), 103)
FROM Table1 GROUP BY id1, id2

But I suspect you need something like this instead:

SELECT A.id1, A.id2, B.MaxDate
FROM Table1 AS A
JOIN
(SELECT id1, CONVERT(varchar(10), MAX(CONVERT(datetime, date)), 103) AS MaxDate
FROM Table1
GROUP BY id1) AS B
ON A.id1 = B.id1 AND A.date = B.MaxDate

[mikr0s]

SELECT MAX(id1) AS id1, MAX(id2) AS id2, MAX(mydate) AS mydate
FROM mytable
GROUP BY id1, id2

--HTH--

[anselme]

ok i'll try it tomorrow morning
thanks a lot