Обновлено: новая проблема (BUG) на fancybox 2: медиа (видео) заставляет страницу идти вверх

Это проблема с наибольшей-n-на-группой, и это очень распространенный вопрос SQL.

Вот как я решаю его с внешними соединениями:

SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4
ORDER BY category_id, date_listed;

I Предполагая, что основным ключом таблицы item является item_id, и что это монотонно возрастающее псевдоименование. То есть большее значение в item_id соответствует более новой строке в item.

Вот как это работает: для каждого элемента есть несколько других элементов, которые новее. Например, есть три элемента новее, чем четвертый новый элемент. Есть ноль, новее, чем самый новый элемент. Поэтому мы хотим сравнить каждый элемент (i1) с набором элементов (i2), которые новее и имеют ту же категорию, что и i1. Если число этих новых элементов меньше четырех, i1 является одним из тех, которые мы включаем. В противном случае не включайте его.

Красота этого решения заключается в том, что он работает независимо от того, сколько у вас категорий, и продолжает работать, если вы меняете категории. Он также работает, даже если количество элементов в некоторых категориях меньше четырех.

Другое решение, которое работает, но использует функцию пользовательских переменных MySQL:

SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (@g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 3;

MySQL 8.0.3 представил поддержку стандартных функций окна SQL. Теперь мы можем решить такую ​​проблему, как это делают другие РСУБД:

WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;