6

Is it possible say for example i want to select a column which does not exist in an mysql table, i just want this column to have incremental values starting from "somevalue (100 maybe)". I want something like this:

my dummy column | other existing columns | blah
100
101
102

is this possible in mysql?

Cadz
  • 139
  • 3
  • 21

1 Answers1

13

Yes sure, You can named column with as keyword. For example:

SELECT 1 as 'hello'

And You can simply add RowNumber column like below example:

SELECT t.*, 
    @rownum := @rownum + 1 as RowNumber
FROM your_table t, 
     (SELECT @rownum := 0) r

For start from 149, You just need to replace (SELECT @rownum := 0) with (SELECT @rownum := 149)

For a dummy column with the certain type, Use Cast or Convert functions like this: CAST(@rownum := @rownum + 1 AS CHAR) AS RowNumber. You can find more about this here: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

More Examples related to the comments:

SELECT
    COUNT(col1) AS col2
FROM your_table
GROUP BY col2
HAVING col2 = 2;

@nodsdorf example in the comments:

SELECT
    (1+1) AS dummy,
    other_column
FROM table
Mehdi Yeganeh
  • 2,019
  • 2
  • 24
  • 42
  • 1
    another example: SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2; – Mehdi Yeganeh Apr 03 '13 at 20:17
  • i want it to increment along with the other existing columns in the table and i want it to start at 149. – Cadz Apr 03 '13 at 20:19
  • how about if i want to select just a dummy column but i want this column to have a certain format like say for example a "longtext" type – Cadz Apr 03 '13 at 20:46
  • 1
    you can use CAST/CONVERT functions: replace this line of example: CAST(@rownum := @rownum + 1 AS CHAR) AS RowNumber and check this page: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html – Mehdi Yeganeh Apr 03 '13 at 20:57
  • 1
    use math function as a dummy column.. SELECT (1+1) AS dummy, other_column FROM table – nodsdorf Oct 14 '14 at 16:01