Number field in MySQL table with sorting and grouping?

Question

MySQL

There are two tables article (article_id, sort) and category (article_id, category_id, number)
You need to fill in the number field from 1... using sorting on the sort field and so that in each category_id the count starts again from 1, and does not continue, that is it should look like this:
category:
article_id - category_id - number
51 1 1
96 1 2
12 1 3
...
62 2 1
51 2 2
28 2 3
... etc.
For example, if you do it separately for each category, since you haven’t figured out how to do it in a query for all categories at once with a reset to 1 in a new one, then like this:

UPDATE `category`
JOIN `article` ON `article`.`article_id`=`category`.`article_id`
SET `number`=(select @n:[email protected]+1 from (select @n:=0) as t)
WHERE `category`.`category_id`=123
ORDER BY `sort` ASC

But he writes that you can’t use order by to a table from join
I would like not to make a query for each category separately, since there are a lot of categories, and it would be faster with one query than for each category

Answer 1

It didn’t work out right away without additional gestures, so I’ll offer this option for now (further scripts with create’s. I didn’t remove them - it seems to me that it will be “more visual” this way):

-- Ваша таблица article
CREATE TABLE a (
a_id INT( 10 ) NOT NULL ,
s INT( 10 ) NOT NULL
) ENGINE = MYISAM ;

INSERT INTO  a (a_id , s) VALUES 
(1, 3), 
(2, 5), 
(3, 7), 
(4, 2), 
(5, 4);

-- Ваша таблица category
CREATE TABLE c (
a_id INT( 10 ) NOT NULL ,
c_id INT( 10 ) NOT NULL ,
n INT( 10 ) NOT NULL DEFAULT 0
) ENGINE = MYISAM ;

INSERT INTO c (a_id , c_id , n) VALUES 
(1, 2, 0), 
(2, 2, 0), 
(3, 1, 0), 
(4, 2, 0), 
(5, 1, 0);

-- В таблицу категорий добавляем временное поле, 
ALTER TABLE c ADD s_tmp INT NOT NULL DEFAULT '0';
-- в которое закинем соответствующие значения sort из article
UPDATE c 
SET c.s_tmp = ( 
  SELECT s
  FROM a
  WHERE a.a_id = c.a_id 
);

-- Создадим временную таблицу, в которую с помощью как раз той магии, которая вам, собственно, нужна, проставим позиции внутри групп
DROP TABLE IF EXISTS c_tmp;

SET @cat:='', @num:=1;

CREATE TABLE c_tmp 
SELECT c_id, a_id,
  @num := IF( @cat = c_id, @num +1, 1 ) AS row_num, 
  @cat := c_id AS dummy
FROM c
ORDER BY c_id, s_tmp;

-- Теперь эти сгенерированные значения перекидываем в основную таблицу
UPDATE c 
SET c.n = ( 
  SELECT row_num
  FROM c_tmp AS ct
  WHERE c.a_id = ct.a_id AND c.c_id = ct.c_id 
);

-- Ну и убираем 
ALTER TABLE  c DROP  s_tmp;
-- за собой мусор
DROP TABLE  c_tmp;

Answer 2

UPDATE category SET number = 0;

SET @old_category = -1;
SET @num = -1;
UPDATE category AS c
INNER JOIN (
  SELECT 
      t.article_id,
      t.sort,
      t.category_id,
      @old_category,
      @num := CASE
      	WHEN @old_category <> t.category_id THEN 0
          ELSE @num + 1
      END AS number,
      @old_category := t.category_id
  FROM (
    SELECT
      a.article_id, 
        a.sort, 
        c.category_id
    FROM category AS c
    INNER JOIN article AS a ON a.article_id = c.article_id
    ORDER BY c.category_id, a.sort
  ) AS t
) AS t
ON t.article_id = c.article_id
SET c.number = t.number;

SELECT c.*, a.sort FROM category AS c 
INNER JOIN article AS a ON a.article_id = c.article_id
ORDER BY c.category_id, a.sort

Result:

aricle_id | category_id | number | sort
5 | 1 | 0 | 4
3 | 1 | 1 | 7
4 | 2 | 0 | 2
1 | 2 | 1 | 3
2 | 2 | 2 | 5