How can I add a unique column to the union result?

Question

Let say here is my query

  SELECT id as people_ID FROM user
    UNION 
  SELECT id as people_ID FROM client

What I want is give every row a unique ID that will increment by adding new column as below:

+------+-------------+
|  ID  |  people_ID  |
+------+-------------+
|  1   |    Alex     |
+------+-------------+
|  2   |   Kenny     |
+------+-------------+
|  3   |   David     |
+------+-------------+
|  4   |  Ashley     |
+------+-------------+

Is it possible to accomplish it?

you could try `set id=0; select @id := @id + 1 from user ... union select @id:=@id+1 from client` But there's no guarantee as to which order the subqueries would execute and return data. — Marc B, Dec 03 '14 at 17:49
possible duplicate of [MySQL query: Using UNION and getting row number as part of SELECT](http://stackoverflow.com/questions/6122440/mysql-query-using-union-and-getting-row-number-as-part-of-select) — Anil, Dec 03 '14 at 17:51

score 0 · Accepted Answer · answered Dec 03 '14 at 18:05

0

Depends which database you use but you could probably do it with rowid as a first column

answered Dec 03 '14 at 18:05

Sheremet

32
3

How can I add a unique column to the union result?

1 Answers1