Row number in mySQL

Question

Is it possible to get 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 third row in this table? After:

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

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

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

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

Is there a way to make "pos" be "3" as I need? (An order would also be important, regardless of whether it is relevant or not.)

Answer 1

If your identifiers strictly increase with line numbers, you can do

 SELECT COUNT(*) FROM tbl WHERE ID <= (SELECT ID FROM tbl WHERE tag = 'C'); 

I'm not sure what you mean by order.

Side Note: Your Code

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

does not work, because here @pos only works with a result set consisting of only one record.

Answer 2

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' 

Answer 3

Actually, there is one possibility that I have not considered before:

 SELECT count(1) FROM table WHERE tag <= 'C' ORDER BY tag 

It looks the same, and a little faster. Or am I missing something?