show row number in select query

Question

what is the problem with this query? it shows null in rowno column.

SELECT @rowno:=@rowno+1 `rn`,`id`, `title`, `topic` 
FROM stories,(SELECT @rownum:=0) r 
WHERE newstype='2';

i run it in 'MySQL Query browser'

thanks in advance.

Answer 1

You have a few problems there:

• You need to initialise @rowno by adding set @rowno = 0 before the query.
• You're missing an as in @rowno:=@rowno+1 rn.
• The (SELECT @rownum:=0) r is superfluous, unless you meant this to be the initialisation for @rowno in which case you misspelt it.

This should work:

SET @rowno = 0;
SELECT @rowno:=@rowno+1 as `rn`, `id`, `title`, `topic` 
FROM stories 
WHERE newstype='2';

Answer 2

In the increment you are using rowno but in the initial assignment you are using rownum