Row number in mySQL

Question

Is it possible to get the row number in MySQL? Say I have a 'table'

ID  tag name
1   A   alpha
4   B   beta
5   C   gamma
8   D   ceta

How can I get in MySQL that, for example, 'C' is the 3rd row in that table? Following:

SET @pos=0;
SELECT @pos:=@pos+1,tag FROM table ORDER BY tag ASC;

counts the rows as it should. But (sorry for ignorant code)

SET @pos=0;
SELECT @pos:=@pos+1,tag FROM table where tag='C' ORDER BY tag ASC;

gives 1 row as a result, with pos as 0, as it probably should.

Is there a way to get the 'pos' to be '3' as I need it to be? (Ordering would be important as well, whether it is relevant to the question or not..)

Answer 1

You can use this

Select rownum from (
SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, table t order by tag) as t1
where tag = 'C'