要实现这样的结果SQL语句应怎样写?

Question

如有一表TMPTABLE如下：
field1        field2       field3
aaaa           15            XXX
aaaa           19            BBB
cccc           33            EEE
dddd           ff            GGG我用样样的语句查出来是这样的
select rownum,field1,field2,field3 from tmptable;rownum   field1        field2       field3
1        aaaa           15            XXX
2        aaaa           19            BBB
3        cccc           33            EEE
4        dddd           ff            GGG但我想要的是如下结果，请问如何做？
unkown  field1        field2       field3
1        aaaa           15            XXX
2        aaaa           19            BBB
1        cccc           33            EEE
1        dddd           ff            GGG即field1相同时显示顺序,不同时出1开始。

Answer 1

select row_number() over(partition by field1 order by field2) rn,field1,field2,field3 from tmptable;