"SELECT id, title, @natusort:=@natusort + 1 AS ordercount" does not increment as expected

3627 views sql mysql database mysqli
13

My query in MySQL does not behave as expected.

SET @natusort := 0;

SELECT id, title,  @natusort:=@natusort + 1 AS ordercount
FROM categories
JOIN table1 ON id = table1.parentid
ORDER BY title LIMIT 10

I expected a set of results like this:

ID  title   ordercount
------------------------------------
67  aaa     1   
23  aab     2
65  aac     3
47  aad     4
78  aba     5
32  abc     6
43  abd     7
33  aca     8
46  acb     9
12  acd     10

But I got this set instead:

ID  title   ordercount
------------------------------------
67  aaa     12  
23  aab     3
65  aac     12
47  aad     34
78  aba     4
32  abc     36
43  abd     31
33  aca     15
46  acb     19
12  acd     50

How can I get the increment to work sequentially starting from 1 and follow the order by?

answered question

2 Answers

5

You can use ROW_NUMBER(), as in:

SELECT id, title,
  row_number() over(order by title) as ordercount
FROM categories
JOIN table1 ON id = table1.parentid
ORDER BY title 
LIMIT 10

posted this
10

The best fix here might be to use ROW_NUMBER, if you are using MySQL 8+. If you must stick with your current approach, then wrap in a subquery before generating the sequence:

SELECT id, title,  @natusort:=@natusort + 1 AS ordercount
FROM
(
    SELECT id, title
    FROM categories
    INNER JOIN table1 ON id = table1.parentid
    ORDER BY title
    LIMIT 10
) t;

posted this

Have an answer?

JD

Please login first before posting an answer.