asked on 3/31/2013

populating table column based on order

I have a table with the following columns
Id
Date
Class
Seq

The last column is currently empty. I need to populate it based ordered date grouped by class, so that the earliest record within a given class has a value of 1, next has 2 and so on.
Engine is mysql.

Thank you!

Databases MySQL Server SQL

Last Comment

cyber-33

4/1/2013

Ioannis Paraskevopoulos

3/31/2013

Hi,

You may try this:

CREATE TABLE Table1 (id int, Date Datetime, Class varchar(10),Seq int);
INSERT INTO Table1 VALUES (1, '20130201', 'a',NULL);
INSERT INTO Table1 VALUES (2, '20130202', 'a',NULL);
INSERT INTO Table1 VALUES (3, '20130203', 'a',NULL);
INSERT INTO Table1 VALUES (4, '20130204', 'b',NULL);
INSERT INTO Table1 VALUES (5, '20130205', 'b',NULL);
INSERT INTO Table1 VALUES (6, '20130206', 'c',NULL);
INSERT INTO Table1 VALUES (7, '20130207', 'd',NULL);


UPDATE Table1 dest,(
SELECT  o.id
       ,o.Date
       ,o.Class
       ,@row_num := IF(@prev_value=o.Class,@row_num+1,1) AS Seq
       ,@prev_value := o.Class
  FROM Table1 o,
      (SELECT @row_num := 1) x,
      (SELECT @prev_value := '') y
  ORDER BY o.Class
  ) src
SET dest.Seq = src.Seq
WHERE dest.Id = src.Id

SELECT * FROM Table1

Open in new window

As you may see though i inserted NULL in Seq, when i select the results after the update Seq is populated.

Giannis

ol muser

3/31/2013

The above solution by @jyparask should work as long as you have at least one unique identifier(as in the column ID above). Just remember to disable safe mode, by un-checking the option in Preferences -> SQL Queries and reconnect.

cyber-33

3/31/2013

ASKER

Looks pretty elegant.
I dont see what in this query will keep the records within each class ordered by date?

Also, why disable the safe mode?

ASKER CERTIFIED SOLUTION

Ioannis Paraskevopoulos

3/31/2013