0

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.

Edited by blessanm: n/a

2
Contributors
15
Replies
16
Views
8 Years
Discussion Span
Last Post by urtrivedi
Featured Replies
  • I found solution in mysql. [code] 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; [/code] Read More

0

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>";

Edited by urtrivedi: n/a

0

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.

2

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;

Edited by urtrivedi: n/a

0

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.

0

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

0

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

0

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

Edited by urtrivedi: n/a

0

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

0

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

0

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

Edited by blessanm: n/a

0

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'?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.