MySQL row number

Question

Say I have a MySQL query, for example:

SELECT id, name, surname FROM employees ORDER BY id

The result woud be:

id    name    surname
 1     Peter   Smith
 2     John    Banjo
...
 1384  Will    Levenstein

While this is an ordered query, I can always assume (as long as I don't change the table) that John Banjo will come out second.

Now what if my query was

SELECT id, name, surname FROM employees WHERE name = 'John' AND surname = 'Banjo'

Could I somehow get what the row number would be in the first query? I'm trying to do this in a much more complicated, but always ordered query, is there any way to archieve this?

@anpher: Suppose Peter Smith is removed, then the first id would be 2. — Johan, Aug 26 '10 at 14:58
@Anpher I can't rely on that. @Johan I need it to backtrace page on which will an item appear (page is limited i.e. to 30 items). — cypher, Aug 26 '10 at 14:59

Michael Pakhantsov · Accepted Answer · 2010-08-26T15:05:37.097

2

 SELECT x.id, x.name, x.surname, x.rownum
 FROM (
      SELECT @rownum:=@rownum+1 rownum, t.*
      FROM (SELECT @rownum:=0) r, employees t
      ORDER BY Id
 ) x
 WHERE x.name = 'John' 
 AND x.surname = 'Banjo'

edited Aug 26 '10 at 15:05

answered Aug 26 '10 at 14:55

Michael Pakhantsov

24,855
6
60
59

This gives me an error (every derived table must have its own alias) – cypher Aug 26 '10 at 15:00
@cypher - added alias for inner query. – Michael Pakhantsov Aug 26 '10 at 15:05

MySQL row number

1 Answers1

Linked