CODEWITHSUNDEEP
phpsqlmysql
swinefeaster
swinefeaster

Reputation: 2555

Add a numbered list column to a returned MySQL query

I am trying to get a sequential number column returned with my sql query. I need this to be inside the SELECT statement because I want to nest this query in another, and then use the column generated before for some further calculations.

I have looked everywhere and I can't find any examples of this.

Something to the effect of the following:

SELECT *, ROW_NUMBER() as row_number from my_awesome_table;

This should generate something like this:

row_number some_column some_other_column
---------- ----------- -----------------
    1        bla bla      bla bla bla 
    2        bla bla      bla bla bla 
    3        bla bla      bla bla bla 
    4        bla bla      bla bla bla

Note that some_column and some_other_column are real existing columns in my_awesome_table, whereas row_number is generated on-the-fly for this query only.

Thanks! swine

Upvotes: 6

Views: 3870

Answers (4)

TehShrike
TehShrike

Reputation: 10084

My favorite way to do what several other people have suggested is this:

SELECT @rownum := (IFNULL(@rownum, 0) + 1), my_awesome_table.*
FROM my_awesome_table

You should be careful using user variables in your SELECT, though - As the docs note,

"In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation."

Different versions of MySQL may produce different results. Test accordingly, and consider numbering your rows in your code, rather than in the query.

Upvotes: 2

shamittomar
shamittomar

Reputation: 46692

You need to use MySQL user variables. Let us take a variable t1. Initialize it at 0. And increment it in every select value.

SET @t1=0;
SELECT @t1 := @t1+1 as row_number, my_awesome_table.* from my_awesome_table

This will do the trick.

Upvotes: 5

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

SELECT @rownum = @rownum + 1 AS row_number, mat.*
      FROM my_awesome_table mat, 
           (SELECT @rownum := 0) r

Upvotes: 0

Ken Downs
Ken Downs

Reputation: 4827

MySQL don't do that.

A little googling found this article:

http://explainextended.com/2009/09/14/mysql-emulating-row_number-with-multiple-order-by-conditions/

Upvotes: -1

Related Questions