MySql Select with auto incrementing column not existing in table

Question

I would like to select from a table and include in this select a column that is not existing in the table and it is a integer auto - incrementing.

Like:

SELECT username, 'c' as C FROM users;

where c doesn't exist in the table, but should be integer and auto-increment.

If it doesn't exist in the table how can you *select* it from the table? — Onimusha, Nov 25 '17 at 19:44
What makes you think this is possible? Why would it be? You can't select something that doesn't exist. If you want an auto-incrementing column, just add one. — elixenide, Nov 25 '17 at 19:44

juergen d · Answer 1 · 2017-11-26T00:52:36.023

1

SELECT username, @rank := @rank + 1 as rank
FROM users
CROSS JOIN (select @rank := 0) r
ORDER BY username

So

CROSS JOIN (select @rank := 0) r

inits a variable named rank. And

@rank := @rank + 1 as rank

increments the variable for every row.

edited Nov 26 '17 at 00:52

answered Nov 25 '17 at 19:45

juergen d

1 Answers1