List:General Discussion« Previous MessageNext Message »
From:Harald Fuchs Date:May 13 2005 9:22am
Subject: Re: Numbering rows
View as plain text  
In article <1115916833.2150.22.camel@stripped>,
Mauricio Pellegrini <hrrg-inf@stripped> 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.

Thread
Numbering rowsMauricio Pellegrini12 May
  • Re: Numbering rowsHarald Fuchs13 May
    • Re: Numbering rowsMauricio Pellegrini13 May
  • Re: Numbering rowsHarald Fuchs17 May