How to Select the Newest Four Items Per Category

How to SELECT the newest four items per category?

This is the greatest-n-per-group problem, and it's a very common SQL question.

Here's how I solve it with outer joins:

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'm assuming the primary key of the item table is item_id, and that it's a monotonically increasing pseudokey. That is, a greater value in item_id corresponds to a newer row in item.

Here's how it works: for each item, there are some number of other items that are newer. For example, there are three items newer than the fourth newest item. There are zero items newer than the very newest item. So we want to compare each item (i1) to the set of items (i2) that are newer and have the same category as i1. If the number of those newer items is less than four, i1 is one of those we include. Otherwise, don't include it.

The beauty of this solution is that it works no matter how many categories you have, and continues working if you change the categories. It also works even if the number of items in some categories is fewer than four.

---

Another solution that works but relies on the MySQL user-variables feature:

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 introduced support for SQL standard window functions. Now we can solve this sort of problem the way other RDBMS do:

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;

Select top N most frequently purchased items in each category

Use row_number() and group by:

SELECT category, item, freq
FROM (SELECT category, item, COUNT(*) AS freq,
             ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(*) DESC) as seqnum
      FROM mytable 
      GROUP BY category, item
     ) ci
WHERE seqnum = 1;

This returns one row per category, even when there are ties for the most common. If you want all possibilities in the case of ties, use rank() instead of row_number().

How to get the latest 2 items per category in one select (with mysql)

Here you go buddy!

SET @counter = 0;
SET @category = '';

SELECT
    *
FROM
(
    SELECT
        @counter := IF(data.category = @category, @counter+1, 0) AS counter,
        @category := data.category,
        data.*
    FROM
    (
        SELECT
            *
        FROM test
        ORDER BY category, date DESC
    ) data
) data
HAVING counter < 2

Select number of rows from each category

Using Dynamic Top
In place of N you can use any number

SELECT Id,Content,Category,createdAt
  FROM (SELECT t.*,
               CASE 
                 WHEN @category != t.category THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @category := t.category AS var_category
          FROM Table1 t
          JOIN (SELECT @rownum := NULL, @category := '') r
      ORDER BY t.Category,t.createdAt DESC) x
 WHERE x.rank <= 'N'

In place of N you can use any number

Output

ID  Content Category    createdAt
6   test6   cat1    2018-03-26T18:23:17Z
2   test2   cat1    2018-03-26T18:22:46Z
5   test5   cat2    2018-03-26T18:23:13Z
4   test4   cat2    2018-03-26T18:23:11Z 

Live Demo

http://sqlfiddle.com/#!9/00ca02/20

Select Data from multiple table and Group by Category

Try this query.. Here category table brought outside and added a Join with prod table in order to make a grouping.

SELECT categories.id, categories.name,
SUM(products.open_stock) AS open_balance,
SUM( (SELECT IFNULL(SUM(stocks.qty),0) FROM stocks WHERE stocks.pid=products.id AND stocks.Indate <= CURDATE()-1) ) AS total_stock_in_jana,
SUM( (SELECT IFNULL(SUM(loadings.qty),0) FROM loadings WHERE loadings.pid=products.id AND loadings.Outdate <= CURDATE()-1) ) AS total_loadings_jana,
SUM( (SELECT IFNULL(SUM(stocks.qty),0) FROM stocks WHERE stocks.pid=products.id AND stocks.Indate = CURDATE()) ) AS total_stock_in_today,
SUM( (SELECT IFNULL(SUM(loadings.qty),0) FROM loadings WHERE loadings.pid=products.id AND loadings.Outdate = CURDATE()) ) AS total_loadings_today
FROM categories INNER JOIN products ON categories.id = products.category_id GROUP BY categories.id;

Get multiple rows from a table to per row of another table

It could make use of a calculated row_number per category.

--
-- Emulated row_number via variables
-- 
SELECT sc.id, id_store
, cat.name AS cat_name
, cat.type AS cat_type
, rn
FROM
(
  SELECT sc.*
  , @rn := CASE 
           WHEN @cat_id = sc.id_category
           THEN @rn + 1 ELSE 1
           END AS rn
  , @cat_id := sc.id_category as cat_id
  FROM stores_categories sc
  CROSS JOIN (SELECT @cat_id:=0, @rn:=0) vars
  ORDER BY sc.id_category, sc.id_store DESC, sc.id DESC
) sc
LEFT JOIN categories cat 
  ON cat.id = sc.id_category
WHERE rn BETWEEN 1 AND 24
ORDER BY sc.id_category, sc.id_store DESC, sc.id DESC

Or

--
-- Grouped self-join
-- 
SELECT sc.id, sc.id_store
, cat.name AS cat_name
, cat.type AS cat_type
, COUNT(sc2.id_store) AS cnt
FROM stores_categories sc
LEFT JOIN stores_categories sc2
  ON sc2.id_category = sc.id_category 
 AND sc2.id_store >= sc.id_store
LEFT JOIN categories cat 
  ON cat.id = sc.id_category
GROUP BY sc.id
HAVING cnt BETWEEN 1 AND 24
ORDER BY sc.id_category, sc.id_store DESC;

Or

--
-- Correlated sub-query
-- 
SELECT sc.id, id_store
, cat.name AS cat_name
, cat.type AS cat_type
, ( select count(*)
    from stores_categories sc2
    where sc2.id_category = sc.id_category 
      and sc2.id_store >= sc.id_store
  ) as rn
FROM stores_categories AS sc
LEFT JOIN categories cat 
  ON cat.id = sc.id_category
HAVING rn BETWEEN 1 AND 24
ORDER BY sc.id_category, sc.id_store DESC;

Demo on db<>fiddle here

MySQL - 5 posts per category

Take use of session variables in MySQL since currently it doesn't support any analytic function. The subquery below will generate sequential number for each category_title and used that column to filter in the outer query.

SELECT  post_title, category_title, description
FROM
    (
        SELECT  p.title AS post_title, 
                c.title AS category_title, 
                c.description 
                @counter := IF(@current_category = c.title, @counter + 1, 1) AS counter,
                @current_category := c.title
        FROM    posts p, distributions d, categories c
        WHERE   p.id = d.post_id
                AND d.category_id = c.id
                AND d.main = 1
                AND ABS(DATEDIFF(NOW(), p.created_on)) > 90
        ORDER   BY p.created_on DESC
    ) s
WHERE   counter <= 5

Although the structure is not the same, but this DEMO will show you result of what the query does.

---

Related Topics