CODEWITHSUNDEEP
mysqlsql
hd.
hd.

Reputation: 18366

show row number in select query

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.

Upvotes: 4

Views: 22140

Answers (2)

moinudin
moinudin

Reputation: 138497

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';

Upvotes: 8

user330315
user330315

Reputation:

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

Upvotes: 6

Related Questions