In article <[EMAIL PROTECTED]>,
Mauricio Pellegrini <[EMAIL PROTECTED]> writes:
> This is the table I have
> Column Id is primary key and auto_numeric
> -----------------------------------------
> Id order item value col_type
> -----------------------------------------
> 1 3 15 0 null
> 2 3 15 5 null
> 3 3 15 0 null
> 4 8 22 7 null
> 5 8 22 0 null
> 6 10 64 20 null
> -----------------------------------------
> And this is the result I would like to obtain.
> Where column col_type should be filled with a number
> representing the ordinal number for the row within the
> group formed by order,item
> -----------------------------------------
> Id order item value col_type
> -----------------------------------------
> 1 3 15 0 1 <--This is row number 1 in the group
> 2 3 15 5 2 formed by Order 3 and Item 15
> 3 3 15 0 3
> 4 8 22 7 1 <-This is row number 1
> 5 8 22 0 2 <-This is row number 2
> 6 10 64 20 1 <-This is row number 1
> -----------------------------------------
(I've renamed "order" to "xorder" since ORDER is a reserved word.)
You could use the old MyISAM/AUTO_INCREMENT trick:
CREATE TEMPORARY TABLE tmp (
id INT UNSIGNED NOT NULL,
xorder INT UNSIGNED NOT NULL,
item INT UNSIGNED NOT NULL,
col_type INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (xorder, item, col_type)
) TYPE=MyISAM;
INSERT INTO tmp (id, xorder, item)
SELECT id, xorder, item
FROM tbl;
UPDATE tbl, tmp
SET tbl.col_type = tmp.col_type
WHERE tbl.id = tmp.id;
When you use an AUTO_INCREMENT column as the last part of a PRIMARY
KEY, you'll get a new sequence for all combinations of the other key
parts - but only for MyISAM tables.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
