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.

Row Count on all Tables in Database?

I'm looking for a way to get a Row count on all of the tables in a Database.
Is there a slick way of doing a
SELECT COUNT(*)
FROM (All Tables)
Thanks,
Scott<-http://www.aspfaq.com/2428
"Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
news:%23UlX2vJ4FHA.3588@.TK2MSFTNGP15.phx.gbl...
> I'm looking for a way to get a Row count on all of the tables in a
> Database.
> Is there a slick way of doing a
> SELECT COUNT(*)
> FROM (All Tables)
> Thanks,
> Scott<-
>|||Perfect, thank you!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ur6EN0J4FHA.1396@.TK2MSFTNGP12.phx.gbl...
> http://www.aspfaq.com/2428
>
> "Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
> news:%23UlX2vJ4FHA.3588@.TK2MSFTNGP15.phx.gbl...
>