pel

Че-т не вышло сделать сходу без дополнительных телодвижений, поэтому предложу пока такой вариант (дальше скрипты с 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;