Out of memory for query

[David Wynter]

Hi,

I have a table with over 8 M rows. I need to select the rows with the most recent date value in a col3 when grouped by 2 other columns, col1 and col2. There is also a auto increment primary key, ID.

I put this query together to see if the number returned was about right, shoulds be about 1/3 of the 8M rows.

SELECT COUNT(*) FROM tbl1 f1
WHERE EXISTS (
SELECT ID FROM tbl1 f
WHERE f.col1 = f1.col1 AND f.col2 = f1.col2
GROUP BY col1, col2
ORDER BY col3 DESC
LIMIT 1)

I got an error "Out of Memory (Needed 2579824)" and my Db server only has 1.5GB. It makes me think I maybe have the query wrong. What is the most efficient way of achieving the desired result. I could use HAVING MAX(col3) = col3 instead of the ORDER BY and LIMIT I suppose. But I thought this would be faster using the LIMIT. I have an index on col3

Hints?

Thx

David