Auto incrementing in a select statement 1

[MacTommy]

Can I put something in a SELECT statement that adds a new value for me for every row, preferrably starting at some particular value.

What I mean is, normally, you say:

> SELECT word FROM words WHERE word LIKE 'a%';
+------+
| word |
+------+
| a    |
| aa   |
| aaa  |
+------+

And what I would like is for it to be like this:

> SELECT <<I..don't..know..yet,..start..at..5>> AS new, word FROM words WHERE word LIKE 'a%';
+----+------+
|new | word |
+----+------+
| 5  | a    |
| 6  | aa   |
| 7  | aaa  |
+-----------+

Is that possible..?!?

Thanks!

 

[MacTommy]

Yes, that was exectly what I meant!
Great!

Just to summarize it, this would be the query for my original question:

SELECT @myIndex:=@myIndex+1 new, word
FROM words ,(SELECT @myIndex:=4) myTmpTable
WHERE word LIKE 'a%';

Which indeed gives me:

+----+------+
|new | word |
+----+------+
| 5  | a    |
| 6  | aa   |
| 7  | aaa  |
+-----------+

Marvelous. Thanks a lot, johnwm!

 

[johnwm]

You're very welcome - thanks for the *

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints