Че-т не вышло сделать сходу без дополнительных телодвижений, поэтому предложу пока такой вариант (дальше скрипты с create'ами. Убирать их не стал — мне кажется, так «нагляднее» будет):
-- Ваша таблица 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;
