SQL a numbering column - MySQL

Question

This is a sql newbie question.

Basically, I want an extra column to return with my select statement to number the rows. I'm using mysql.

Eg:

select * from friends

David
Steve
Joe

What is the syntax to get:

1  David
2  Steve
3  Joe

Answer 1

In SQL Server 2005 and higher you can use the Row_Number function.

Answer 2

I'll do this in a stored proc

CREATE TABLE #Foo (
  [FooId] [int] IDENTITY(1,1) NOT NULL,
  [Name] varchar(255)
)

SELECT *
FROM Friends
INTO #Foo

SELECT *
FROM #Foo

Answer 3

From: http://markmal.blogspot.com/2006/04/oracle-like-rownum-in-mysql.html

SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, mytable t;

Answer 4

As you might have guessed from the divergent responses you've received, the answer depends on which DBMS you are using. However, the answer typically involves ROWNUM or a variant on that spelling.

The standard SQL answer is probably ROW_NUMBER() - mentioned by @YonahW; it works as a windowing function and is not available in all DBMS.

Answer 5

If you are developing some application, its better to get the number of records from your result set or data set or any container you are using to hold the result, instead. The basic idea is to do it in your code. Otherwise, Paul solution will work in your case.

In MySql, there is nothing like ROWNUM, what we have in Oracle, and neither we have Row_Number(), what we have in MS SQL Server, nor any other variant.

Edit:

By doing it in your code, you need not change your SQL statement, if the underlying database changes, just because of this reason.