How to show sequential number in MySQL query result

Question

I have some simple query:

SELECT foo, bar FROM table

i think you now whats the result looks like.

What I want to do is to show some sequential number based on how many data appear from query result. its just like AUTO_INCREMENT(its not mean i want to show ID). The result what I want is like:

|No|   foo   |    bar   |
-------------------------
|1 |   bla   |    123   |
|2 |   boo   |    abc   |
|3 |   wow   |    xxx   |

How should I do to make it?

thanks in advance

Gerthagerti

Answer 1

select @rownum:=@rownum+1 No, foo, bar from table, (SELECT @rownum:=0) r;

Gingery

its not like mysql query, is it? – Gerthagerti

@Harry joy: i'm sorry for my limited mysql knowledge. – Gerthagerti

@bungdito: are you have some recomended tutorial for this? i really new for that query. terima kasih atas bantuannya. – Gerthagerti

forums.mysql.com/read.php?10,36490,36511 or jimlife.wordpress.com/2008/09/09/… – Gingery

Answer 2

The order gets scrambled if you are using GROUP BY clause in your query. The work around is putting your query inside the FROM clause like this.

SET @a:=0;
SELECT @a:=@a+1 No, output.*
FROM (
     SELECT foo, bar
     FROM table GROUP BY foo, bar
 ) output;

Soporific

Answer 3

Neither of the answers worked for me, but based on bungdito's source, I realized you can do something easier:

SET @a:=0;
SELECT @a:=@a+1 No, foo, bar
FROM table;

So, first make sure SET @a:=0; runs. That will set up your variable, a. Then you can increment it in the results with @a:=@a+1. I tested this with GROUP BY, ORDER BY, even JOINS and it worked as expected.

Quiescent

Nice answer! Thanks for sharing. Your answer helped me, but I think it is far more readable when the AS keyword is used. This would also help beginners, like me, who may be unaware that the AS keyword can be omitted. I just discovered this last night. If a comma is accidentally deleted or forgotten you'll get a syntax error when using AS clause, but if you don't have an AS clause it will simply alias col1 as col2 with no indication despite it not being the intended behavior. But indeed a nice shorthand for advanced users like yourself. +1 for simplicity and readability to other answers. Thanks – Tequila

Does not work for me when I use ORDER BY. The row number seems to be assigned before the sorting happens, so the row number is not ascending in the result. – Nob

@TilmanVogel Did you come up with a solution? – Fermentation

@MagnusLindOxlund I actually did the numbering in client-code separate from the query but other than that, I would go for ROW_NUMBER() today as seen below: https://mcmap.net/q/514190/-how-to-show-sequential-number-in-mysql-query-result – Nob

@MagnusLindOxlund Two other options would be using a CTE (if available) WITH r AS (SELECT ....) SELECT @a:=@a+1 nr, r.* FROM r; or a temporary table CREATE TEMPORARY TABLE r AS SELECT ...; SELECT @a:=@a+1 nr, r.* FROM r;. – Nob

Answer 4

If somebody wants to display the row number after ordering records, something like this may work

set @a=0; 
select @a:=@a+1 serial_number,t.* 
from ( SELECT foo, bar FROM tableORDER BY bar ) t

Thievery

This in general does unfortunately not work, see mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored – Nob

Answer 5

I think this could be best achieved by:

select my_column, row_number() over (order by my_column) as No from my_table

Muzzleloader