0

I have a table named Person like:

Name | Surname | Age
--------------------
Foo  | Bar     | 23
Faz  | Caz     | 17
Cas  | Zas     | 26

Is there any 'easy' way (function or something) to create new auto increment column while using select query? But just for result, no real change in database. For instance if I use:

SELECT 'test' as new_col, Name, Surname, Age FROM Person;

I would get result with new column new_col. I want exchange this 'test' with raising by 1 number for each column: 1, 2, 3 etc.

Expected result:

new_col | Name | Surname | Age
------------------------------
   1    | Foo  | Bar     | 23
   2    | Faz  | Caz     | 17
   3    | Cas  | Zas     | 26

I know I can create function or procedure with variables but maybe there is easier way that I am not aware of?

I will appreciate any help!

Shadow
  • 33,525
  • 10
  • 51
  • 64
Adrian Kurzeja
  • 797
  • 1
  • 11
  • 27

1 Answers1

1

You can use row_number():

SELECT ROW_NUMBER() OVER (ORDER BY NULL) as new_col, 'test' as new_col, Name, Surname, Age
FROM Person;

Or in older versions of MySQL:

SELECT (@rn := @rn + 1) as new_col, 'test' as new_col, p.Name, p.Surname, p.Age
FROM Person p CROSS JOIN
     (SELECT @rn := 0) params
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786