rownum and sub rownum

I have a table with six records. 4 A records and 2 B records.
how do i count them by A and B. when I do a
select rownum, col from table;
i get:
1 A
2 A
3 A
4 A
5 B
6 B

How can I get the following result?
1 A 1
2 A 2
3 A 3
4 A 4
5 B 1
6 B 2

help PleaseWhat DBMS are you on? For Oracle there is the ROW_NUMBER function:

select row_number() over (order by col),
col,
row_number() over (partition by col order by 1)
from table;|||I am on Oracle. This worked. Thank you very much|||Hi, I am unable to understand what this criteria means:

ID = '"& request.querystring("oID") & "'

in the following statement:

select count(distinct hazmatclass) as hzcount
from manifestexp
where orderkey in
(
select orderkey
from manifestexp
where ID = '"& request.querystring("oID") & "'
)
group by hazmatclass;

* manfestexp is a view.
* ID is a column in that view.

Any hints please??|||That is (bad) ASP syntax. Someone is building a SQL statement as a character string in ASP, and concatenating into it an ID value from a field on a form.

I say bad syntax, because what they should be doing is using bind variables via a Prepared Statement.|||Thank you very much for your feedback.
I am not sure I understand the prepared statement part. How do I go about doing that.

Originally posted by andrewst
That is (bad) ASP syntax. Someone is building a SQL statement as a character string in ASP, and concatenating into it an ID value from a field on a form.

I say bad syntax, because what they should be doing is using bind variables via a Prepared Statement.