C, PHP, VB, .NET

Нека имаме таблица с имена на студенти и съответни точки, които са получили на контролна работа. Повечето точки са по-добър резултат. Таблицата е следната:
DROP TABLE IF EXISTS score;
CREATE TABLE score(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32) CHARACTER SET UTF8,
score TINYINT UNSIGNED
) ENGINE=InnoDB;

INSERT INTO score
VALUES (1,'Иван',95),(2,'Мария',97),(3,'Георги',85),
(4,'Михаил',92),(5,'Тодор',92);
Искаме да изкараме класиране, т.е. да определим кой е първи, кой втори, трети и т.н. по услех. Класическото решение е с използване на Self join:
SELECT t1.id, t1.name, t1.score, COUNT(DISTINCT t2.score) AS rank
FROM score AS t1 JOIN score AS t2 ON (t1.score <= t2.score)
GROUP BY t1.id
ORDER BY rank, t1.name;
Това решение се дава масово, но за съжаление е прекалено бавно. В дневника на Shlomi Noach e дадено решение с огромно ускорение на бързодействието. В неговото решение се използват сесийни променливи:
SELECT id, name, score, rank
FROM(
SELECT id, name, score,
@prev := @curr,
@curr := score,
@rank := IF(@prev = @curr, @rank, @rank+1) AS rank
FROM score JOIN
(SELECT @curr := null, @prev := null, @rank := 0) sel1
ORDER BY score DESC, name ASC
) AS t;
Друго, доста подобно по философия, решение (източник) е следното:
SELECT score.id, score.name, score.score,
CASE
WHEN @prev = score.score THEN @rank
WHEN @prev := score.score THEN @rank := @rank + 1
END AS rank
FROM (SELECT @prev:=NULL, @rank:=0) AS t
JOIN score
ORDER BY score.score DESC, score.name;
Roland Bouman предлага малко по-бавен метод, който за сметка на това можем да примем за по-сигурен. При него всички записи в "score" се конкатенират в едно множество с GROUP_CONCAT, след което с FIND_IN_SET се търси позицията на текущия елемент в това множество.

SET @@group_concat_max_len := @@max_allowed_packet;
SELECT id, name, score,
   FIND_IN_SET(score,
               (SELECT GROUP_CONCAT(
                        DISTINCT score
                        ORDER BY score  DESC
                       )
                FROM score
               )
   ) as rank
FROM score
ORDER BY rank, name;

Идеята на Бауман е да се избегне използването на сесийни променливи вътре в самата заявка, защото теоретично това може да доведе до различни резултати в зависимост от плана за изпълнение на заявката. Аз лично бих оптимизирал съвсем леко заявката на Бауман по следния начин:
SET @@group_concat_max_len := @@max_allowed_packet;
SELECT GROUP_CONCAT(DISTINCT score ORDER BY score DESC)
INTO @tmp_group
FROM score;

SELECT id, name, score,
FIND_IN_SET(score, @tmp_group) as rank
FROM score
ORDER BY rank, name;
Проблемът на всички предложени досега решения е, че резултатът всъщност не е коректен за практическа употреба. Всички представени заявки генерират следния резултат:

+----+--------+-------+------+
| id | name   | score | rank |
+----+--------+-------+------+
|  2 | Мария  |    97 |    1 |
|  1 | Иван   |    95 |    2 |
|  4 | Михаил |    92 |    3 |
|  5 | Тодор  |    92 |    3 |
|  3 | Георги |    85 |    4 |
+----+--------+-------+------+

Аз не съм съгласен с този резултат, защото от пет човека Георги трябва да е на пето място, а не на четвърто! Така поне са класиранията в различните спортни състезания.

Най-очевидното решение на този проблем е една малка модификация на заявката на Ноуч. В нея ще добавим още една променлива - "стъпка". Когато двама или повече хора споделят един и същи rank, то за всеки от тях ще увеличаваме стъпката с единица. Така човекът след тях ще получи ранг увеличен с тази стъпка (а при него стъпката ще се връща обратно на 1). Ето и решението:
SELECT id, name, score, rank
FROM(
SELECT
id, name, score,
@step := IF(@prev = @curr, @step+1, 1) AS step,
@prev := @curr AS previous,
@curr := score AS current,
@rank := IF(@prev = @curr, @rank, @rank+@step) AS rank
FROM
score JOIN
(SELECT @curr := null, @prev := null, @rank := 0, @step := 1 ) AS vars
ORDER BY score DESC, name ASC
) AS t;
Изпълнението на тази заявка дава коректен резултат:

+----+--------+-------+------+
| id | name   | score | rank |
+----+--------+-------+------+
|  2 | Мария  |    97 | 1    |
|  1 | Иван   |    95 | 2    |
|  4 | Михаил |    92 | 3    |
|  5 | Тодор  |    92 | 3    |
|  3 | Георги |    85 | 5    |
+----+--------+-------+------+

Добавянето на допълнителната променлива за стъпка не забавя значимо заявката. Остава обаче съмнението заради теоретично възможната некоректност на резултата в по-специфични ситуации. Демонстрация за това е модификацията на "CASE" метода:

SELECT id, name, score, rank
FROM(
SELECT score.id AS id, score.name AS name, score.score AS score,
@step := IF(@prev = score.score, @step+1, 1) AS step,
   CASE
   WHEN @prev = score.score THEN @rank
   WHEN @prev := score.score THEN @rank := @rank + @prev_step
   END AS rank,
@prev_step := @step AS step_backup
FROM (SELECT @prev:=NULL, @rank:=0, @step:=1, @prev_step:=1) AS t1
JOIN score
ORDER BY score.score DESC, score.name
) AS t2;

Както виждате има едно паразитно @prev_step. Променливата @step не се използва никъде освен в реда "@prev_step := @step". Не можем ли директно да сложим "@prev_step := IF(@prev = score.score, @prev_step+1, 1) AS step" на негово място и въобще да се лишим от @step? Изпробвайте и ще видите съвсем неочакван резултат. Проблемът идва именно от query execution plan. Тоест Бауман със сигурност е прав, че методите използващи сесийни променливи никак, ама никак не са препоръчителни!

Дали метода на Бауман и Self Join метода не биха могли да бъдат модифицирани така, че да използват въпросната стъпка и да дават коректни резултати? Идеята е в никакъв случай да не се използват сесийни променливи. Дерзайте за идеи :)

П.П. Горните примери са валидни за MySQL. В MSSQL например си има вгладен оператор "RANK() OVER", с който се осъществява точно търсеното решение. За съжаление в MySQL няма такива готови функции. Евентуално биха могли да бъдат реализирани чрез използване на курсор.