sql?

Question

TABLE
col1          col2           col3
 x               1               0
 x               2               1
 x               3               2
 x               3               3
 y               1               0
 y               2               1
 z               1               0
 z               1               1
 z               2               2
 z               2               3How to return 3 records:col1           col2           col3
 x               3               2      or x,3,3
 y               2               1
 z               2               2      or x,2,3

Answer 1

select col1,col2,col3=count(*) from tablename where col2 in(select max(col2) from tablename) group by col2

Answer 2

select A.col1,B.col2,A.col3 from A ,(select col1,max(col2) as col2 from A group by col1) B where A.col1 = B.col1 and A.col2 = b.col2