Need a sequence number for every row in MySQL query

Question

So I found this great use:

SELECT (@row:=@row+1) AS ROW, ID  
FROM TableA ,(SELECT @row := 0) r   
ORDER BY ID DESC

The @row:=@row+1 works great, but I get the row ordered by the ID.

My tables look more like this:

SELECT (@row:=@row+1) AS ROW, ID , ColA, ColB, ColC
FROM TableA 
JOIN TableB on TableB.ID = TableA.ID
JOIN TableC on TableC.ID = TableA.ID 
WHERE ID<500
,(SELECT @row := 0) r   
ORDER BY ID DESC

Note: I noticed that if I remove the JOINs I DO get the requested result (In Which ROW is the sequential number of each row, no matter the ORDER BY of ID). The first example works great but for some reaosn, the JOINs mess it up somehow.

so I get this:

What I am after is:

Here's the SqlFiddle

So it basically seems that the row number is evaluated before the ORDER BY takes place. I need the ORDER BY to take place after row was given.

How can I achieve that?

Taryn · Accepted Answer · 2012-10-03 16:04:51Z

10

Remove the ORDER BY:

SELECT (@row:=@row+1) AS ROW, ID  
FROM table1 ,(SELECT @row := 0) r

See SQL Fiddle with Demo

Then if you want to use an ORDER BY wrap the query in another SELECT:

select *
from 
(
  SELECT (@row:=@row+1) AS ROW, ID  
  FROM table1 ,(SELECT @row := 0) r
) x
order by row

Or if you leave the ORDER BY on the query, then you can see the way the row number is being applied by simply playing with either DESC or ASC order - See Demo

If you use DESC order

SELECT (@row:=@row+1) AS ROW, ID  
FROM table1, (SELECT @row := 0) r
order by id desc;

the results are which appears to be the result you want:

ROW | ID
----------
1   | 15
2   | 10
3   | 2

If you use ASC order:

SELECT (@row:=@row+1) AS ROW, ID  
FROM table1 ,(SELECT @row := 0) r   
ORDER BY ID;

the results are:

ROW | ID
----------
1   | 2
2   | 10
3   | 15

Edit, based on your change, you should place the row number in a sub-query, then join the other tables:

select *
from
(
  SELECT (@row:=@row+1) AS ROW, ID  
  FROM Table1,(SELECT @row := 0) r   
  order by ID desc
) x
JOIN Table2 
  on x.ID = Table2.ID;

See SQL Fiddle with Demo

edited Oct 3, 2012 at 16:04

answered Oct 3, 2012 at 11:38

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ted Over a year ago

Sorry, I had to unmark your answer because admins did not allow me to ask in another question. The thing is that when JOINs are involved, it somehow messes the result. so that ID=2 always gets the ROW=1 and ID=15 get ROW=3. explained in the :edit:

Taryn Over a year ago

@ted well that is a separate question from the original one you asked. please create a sql fiddle with some sample data

Ted Over a year ago

I am sorry about that bluefeet, it was moderators decision

Taryn Over a year ago

@Ted see my edit, you will want to use a subquery to assign the row number and then join on it.

Aaron Bertrand Over a year ago

@Ted can you explain "moderators decision"? Are you getting the message "we are no longer accepting questions from this account"? If so, please read this in full.

|

Joe G Joseph · Accepted Answer · 2012-10-03 11:47:36Z

4

I dont find any problem with your query

SELECT (@row:=@row+1) AS ROW, ID  
FROM table1 ,(SELECT @row := 0) r   
order by ID desc

SQL Fiddle demo

edited Oct 3, 2012 at 11:47

answered Oct 3, 2012 at 11:42

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Collectives™ on Stack Overflow

Need a sequence number for every row in MySQL query

2 Answers 2

6 Comments

SQL Fiddle demo

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related