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!

 

[sleipnir214]

I doubt it's possible without jumping through a lot of hoops. I'd do it in my programming language.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[MacTommy]

Yes, but that is exactly what I want to avoid.
Usually if the is an SQL construct for something simple like this it is faster...

 

[sleipnir214]

I don't see how having MySQL generate the numbers will be significantly faster. Your programming language will probably have to fetch the data from the resultset one row at a time anyway -- adding a counter and an increment isn't going to affect performance that much.





Want the best answers? Ask the best questions! TANSTAAFL!

 

[r937]

whereas adding a counter in the SQL will definitely slow it down

r937.com | rudy.ca

 

[jnunez247]

If you still want to do this in SQL, check out the following:

SQL TIP: Auto Increment in an UPDATE statement.
[URL unfurl="true"]http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx[/url]

This works best if you are using it in a stored procedure.

 

[sleipnir214]

Will that even work in MySQL?



Want the best answers? Ask the best questions! TANSTAAFL!

 

[r937]

holy toledo!!

that page crashes my browser, i can't even read it!!

i most certainly would never do anything that any site said if that site crashes my browser

r937.com | rudy.ca

 

[sleipnir214]

It didn't crash mine, but then I use Opera.

The solution there, which is in a Microsoft SQL Server forum, reads:

To start, I can run the following query to create the temp table:

[tt]SELECT counter = 0, * FROM Users INTO #tmp_Users[/tt]

This creates a temp table named #tmp_USers where the column "counter" is set to 0 for each row.

In order to update the counter column so that each row has a counter value greater than the previous row, run the following statement.

[tt]DECLARE @counter int
SET @counter = 0
UPDATE #tmp_Users
SET @counter = counter = @counter + 1[/tt]



Want the best answers? Ask the best questions! TANSTAAFL!

 

[MacTommy]

Thanks guys!
I thought it would be a lot easier than that.
But now that I know that you all wouldn't know how to do it either (in MySQL, I mean) I can stop looking in this direction.

 

[audiopro]

Sorry to enter the thread late but can I ask what you need the numbers for? There may be a better way of achieving your objective.

Keith

http://www.studiosoft.co.uk

 

[MacTommy]

Well, I needed them because I wanted to do something with the entire result set afterwards in a Perl program, that somehow needed each row to have a number indicating which particular row it was (so first row: 1, second row: 2, etc.).
So I could first extract the data from the database, store it as a hash in Perl and run through it one by one to add this index column. But that seemed a bit slow and useless, since, I thought, MySQL could maybe do it for me in one go.

Now that this doesn't seem the case I have already solved it it a different way, but still I have the feeling that one day I'll run against something in a book or whatever that can get the thing done.
Because somehow it doesn't look to me like a very difficult thing for MySQL to do...

 

[audiopro]

It would depend on how you are processing the retrieved data but the usual way would be

while(@results=$sth->fetchrow_array()){

in which you can process each result however you like.


Keith

http://www.studiosoft.co.uk

 

[MacTommy]

Yes, I see that.
There is always a way to do it, but I just can't bear it that there isn't a way that MySQL can return consecutive numbers.

I mean, what I would have expected is some arithmetic construct that remembers the last value it returned. Or a way to create an AUTOINCREMENT column on the fly.
Something likt that...

 

[audiopro]

How are you processing the results set?

Keith

http://www.studiosoft.co.uk

 

[johnwm]

I had a similar problem for selecting a bunch of images based on various (user selected) criteria. I used this (sorry I can't remember where it came from)

myStr=request("r1")
strSQL="PREPARE stmt2 FROM 'Select mynum,pictitle,description,picpath, picdate,@row_index:=@row_index+1 row_index from pics  ,(select @row_index:=0) ri WHERE (subject=?) order by picdate';"
ObjCon.Execute(strSQL)
If len(myStr)=0 then myStr=""
StrSQL = "SET @a= '" & myStr & "';"
ObjCon.Execute(StrSQL)
StrSQL= "EXECUTE stmt2 USING @a;"

Its (obviously) ASP but I'm sure you can translate into whatever.

Works OK in MySQL4

___________________________________________________________
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

 

[MacTommy]

That looks interesting johnwm.
Quite sure it can be translated into any language, but could you maybe comment a bit on what each line does exactly, because I am afraid I don't really get it in one glance..?!?

 

[sleipnir214]

johnwm:
By what database server is that SQL statement run? To the best of my knowledge, MySQL does not support a PREPARE...FROM query.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[johnwm]

sleipnir:
It's running on MySQL 4.1.21
See

http://dev.mysql.com/doc/refman/4.1/en/sqlps.html

for documentation

MacTommy

myStr=request("r1")  '***gets request string for query parameter
strSQL="PREPARE stmt2 FROM 'Select mynum,pictitle,description,picpath, picdate,@row_index:=@row_index+1 row_index from pics  ,(select @row_index:=0) ri WHERE (subject=?) order by picdate';" '*** my particular query plus row index generator
ObjCon.Execute(strSQL)  '*** do the PREPARE
If len(myStr)=0 then myStr="" '*** catches null
StrSQL = "SET @a= '" & myStr & "';" '*** sets query parameter
ObjCon.Execute(StrSQL)  '*** do it
StrSQL= "EXECUTE stmt2 USING @a;"  '*** run the query
'  *** Then fetch the recordset normally
Set rs = ObjCon.Execute(StrSQL) '*** get recordset
if not rs.eof then '*** check for empty rs
picarray=rs.getrows()  '*** convert to array
myTotal=ubound(picarray,2)  '*** get total
end if
rs.Close  '*** tidy up
 set rs=Nothing
  ObjCon.Close
  Set ObjCon = Nothing

___________________________________________________________
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

 

[johnwm]

sleipnir
Did you find that reference OK?

[b}MacTommy[/b] Was that what you wanted?

___________________________________________________________
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