Help in a loop query

[blessanm]

I have database with a table called orders. The structure is below

Index, OrderIndex, OrderNo
111, 1
112 , 1
113 , 1
114 , 2
115 , 3
116 , 3

I want a query that will loop thorugh the database and make the result

Index, OrderIndex, OrderNo
111, 1, 1
112, 1, 2
113, 1, 3
114, 2, 1
115, 3, 1
116, 3, 2

Im trying to change the OrderNo based on the OrderIndex.

Is there a way to do this purely using sql? If possible please help.

[urtrivedi]

You need not to do it in query, you can do that it one loop using php code. Also you need to add order by statment at the end of your query. i.e. order by index,orderindex

$resulthdr = oci_parse($connection, $queryhdr);


if (!(oci_execute($resulthdr)))
	showerror();

print "\n<font face='sans-serif' size=1 color=#000000>";     
print "\n<table>" ;
print "\n<tr>";
print "\n<td width=225 align=left>INDEX</td>";
print "\n<td width=150 align=left>ORDER INDEX</td>";				
print "\n<td width=225 align=left>ORDER NO</td>";
print "\n</tr>";

$prevorder="";
 while($rowhdr=oci_fetch_assoc($resulthdr))
 {
  	if($prevorder==$rowhdr['INDEX'].",".$rowhdr['ORDERINDEX'])
  	{
		$orderno++;	  	 		
 	}
 	else
 	{
 		$orderno=1;
	}
	print "\n<tr>";
	print "\n<td width=225 align=left>{$rowhdr['INDEX']}</td>";
	print "\n<td width=150 align=left>{$rowhdr['ORDERINDEX']}</td>";				
	print "\n<td width=225 align=left>{$orderno}</td>";
	print "\n</tr>";
	$prevorder=	$rowhdr['INDEX'].",".$rowhdr['ORDERINDEX'];
}
print "</table>";
print "</font>";

[blessanm]

Thanks for the quick reply. I knew it was possible with php. I wondered whether there is method by using just sql query. Thanks for the help.

[urtrivedi]

I found solution in mysql.

set @counter:=0;
set @previndex:=0;
set @prevorderindex:=0;

select index, orderindex, 

@counter:=(case when @previndex=index and @prevorderindex=orderindex then @counter +1 else 1 end) srno, 

@previndex:=index,
@prevorderindex:=orderindex

 from tablename 

order by index,orderindex;

[blessanm]

Thanks for the reply. Wasn't even sure it was possible using sql. Well could u please also tell me when i should make the substitutions in the query for it to work in my table. I tried a couple of combination's but it didn't work.

[urtrivedi]

I have checked above code in phpmyadmin it is working fine. What error you are facing

[blessanm]

SET @counter:=0;
SET @previndex:=0;
SET @prevorderindex:=0;
SELECT index, orderindex,
@counter:=(CASE WHEN @previndex=index AND @prevorderindex=orderindex THEN @counter +1 ELSE 1 END) srno,
@previndex:=index,
@prevorderindex:=orderindex
FROM orders1
ORDER BY index,orderindex;

The above is the query i entered.
'orders1' is the name of my table

This is the error i get

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index, orderindex,
@counter:=(CASE WHEN @previndex=index AND @prevorderind' at line 1

[urtrivedi]

which version of mysql you are using. Subselect works from 4.1.

[blessanm]

5.0.45-log. Thats my version number. Did the query i send work?

[urtrivedi]

Problem is with keyword index, Your table's column name is index so it is giving an error. following is the code with escape character `

SET @counter:=0;
SET @previndex:=0;
SET @prevorderindex:=0;

SELECT `index`, orderindex,
@counter:=(CASE WHEN @previndex=`index` AND @prevorderindex=orderindex THEN @counter +1 ELSE 1 END) srno,
@previndex:=`index`,
@prevorderindex:=orderindex
FROM orders1
ORDER BY `index`,orderindex

[blessanm]

the query executed but the column 'orderno' values didnt change. that column is still null.

[urtrivedi]

I hope you have initialised the three variable. select all four sql statments and execute in phpmyadmin

[blessanm]

The thing is i didnt see the keyword 'orderno' in the query. Dont we have to set it somewhere in the query.

[urtrivedi]

You mean to say srno column

[blessanm]

To be frank im lost. I just want the 'orderno' column to be updated. could please tell me what i shouild modify to get that done. is this 'srno' ,'orderno'?

[urtrivedi]

rename srno to orderno