Avatar of cyber-33
cyber-33
Flag for United States of America asked on

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!
DatabasesMySQL ServerSQL

Avatar of undefined
Last Comment
cyber-33

8/22/2022 - Mon
Ioannis Paraskevopoulos

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

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

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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Ioannis Paraskevopoulos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
cyber-33

ASKER
Have not tested it, but looks pretty good. Thank you!