I have a table with the following structure:

Drwno     Rev       Date    Code
101        0      05/12/06   A
101        1      02/01/07   A
101        1      04/01/07   A
102        1      05/12/06   A
102        1      04/01/07   A

I would like an sql statement that has both the max rev and max date for each drawing number as follows:

Drwno     Rev       Date    
101        1      04/01/07  
101        1      04/01/07  

I have tried

SELECT a.drwno, MAX(a.rev) AS rev,MAX(a.date) as date FROM curShopIssue1 a INTO CURSOR curMaxRev GROUP BY a.drwno

This seems to work for the 'rev' but does not for 'date'. Any suggestions would be greatly appreciated.

Thanks in advance!