Generate Serial Number in SQL

The next query generates results as below.

SELECT address FROM post;

address
----------------
New York, NY 10037, USA
New York, NY 10028, USA
Toronto, ON M1X 1Z9, Canada
Ottawa, ON K2A 3L4, Canada

Suppose we want to add a column with serial numbers, just as below.

id  | address
--- | ----------
 1  | New York, NY 10037, USA
 2  | New York, NY 10028, USA
 3  | Toronto, ON M1X 1Z9, Canada
 4  | Ottawa, ON K2A 3L4, Canada

In MySQL, this can be done by defining a variable in the query:

SELECT @a := @a + 1 id, address
FROM post, (SELECT @a :=0) AS dummy;

Reference:
MySQL User Defined Variables

Comments

comments