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
select @rownum:=@rownum+1 No, foo, bar from table, (SELECT @rownum:=0) r;
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;
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.
ORDER BY. The row number seems to be assigned before the sorting happens, so the row number is not ascending in the result.
ROW_NUMBER() today as seen below: stackoverflow.com/a/78244286/119725WITH 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;.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
I think this could be best achieved by:
select my_column, row_number() over (order by my_column) as No from my_table
