CODEWITHSUNDEEP
mysqlselect
nunu
nunu

Reputation: 2871

How to show sequential number in MySQL query result

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

Upvotes: 27

Views: 39943

Answers (5)

Andres Moreno Abrego
Andres Moreno Abrego

Reputation: 65

I think this could be best achieved by:

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

Upvotes: 0

Arvind Krmar
Arvind Krmar

Reputation: 2602

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

Upvotes: 0

Travis Heeter
Travis Heeter

Reputation: 14054

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.

Upvotes: 9

divinedragon
divinedragon

Reputation: 5346

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;

Upvotes: 12

bungdito
bungdito

Reputation: 3620

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

Upvotes: 50

Related Questions