How to Get the Latest 2 Items Per Category in One Select (With MySQL)

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

MySQL query to get last record in each category

Add ORDER BY id DESC to your query

This is the way of getting the last 10 rows is to reverse the order and select the first ten rows:

SELECT id, title, categoryId, categoryName
FROM ( 
        SELECT a.id, a.title, ac.category AS categoryId, c.name AS categoryName
        FROM articles AS a 
            LEFT JOIN articles_categories AS ac ON ac.article = a.id 
            LEFT JOIN categories AS c ON c.id = ac.category 
        WHERE ac.priority = 1 
        ORDER BY a.id DESC ) AS tmp_table 
GROUP BY categoryId  ORDER BY id DESC LIMIT 10

display last 2 entries in each category from a mysql table

These type of results are best handled by window functions in other RDBMS but unfortunately Mysql don't have any window functions so for alternative there is a solution to use user defined variables to assign a rank for rows that belong to same group

SELECT  `id`, `category`, `names`
FROM (
SELECT *,
@r:= CASE WHEN @g = category THEN @r + 1 ELSE 1 END rownum,
@g:=category
FROM test
  CROSS JOIN(SELECT @g:=NULL ,@r:=0) t
  ORDER BY category,id desc
) c
WHERE c.rownum <=2 

Above query will give you 2 recent records (on basis of id) per category you can change the last part of query with where clause to any number to show n results per group for example to show 3 records then WHERE c.rownum <= 3 and so on

Demo

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 2 products from each category in MySQL

Something along these lines will work:

SELECT id, name, category 
FROM (
  SELECT *, 
         IF( @prev <> category, 
             @rownum := 1, 
             @rownum := @rownum+1 
         ) AS rank, 
         @prev := category, 
         @rownum  
  FROM (
    SELECT * FROM products 
    ORDER BY category, rand()
  ) random_prodcts
) products_ranked 
WHERE rank <= 2;

It orders them randomly within the categories, then pulls them out tracking how many it's got from each.

Not sure how nicely it will scale though.

EDIT: Tried it with a few thousand records and it seems ok.

MySQL Select top 10 items for each category

Does this work?

SELECT 
    yourtable.* 
FROM 
    yourtable 
    JOIN (
        SELECT 
            t1.name, 
            t1.value, 
            COUNT(t2.name) AS theCount 
        FROM yourtable t1 
        LEFT JOIN yourtable t2 ON t1.name = t2.name AND t1.value > t2.value 
        WHERE t1.name in ('a', 'b')
        GROUP BY t1.name, t1.value 
        HAVING theCount < 2
    ) AS dt USING (name, value);

Source: http://thenoyes.com/littlenoise/?p=36

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;

Select ONLY one row per category

Use GROUP BY :

SELECT MIN(id), cat_id, title FROM table GROUP BY cat_id

Select The last 3 news from each category - Two tables - (MySQL - PHP)

You could get n no. of news for each category by using a correlated sub query

SELECT *
FROM news n
JOIN categories c ON c.id = n.category_id
WHERE c.priority > 1
AND (
  SELECT COUNT(*)
  FROM news 
  WHERE n.category_id = category_id
  AND n.id <= id
) <= 3; 

Demo

Or if you are using Mysql 8 then you could make use of window functions with common table expressions like

WITH cte AS(
    SELECT *, 
    RANK() OVER (PARTITION BY category_id ORDER BY id DESC ) rnk
    FROM news
    ORDER BY id

)

SELECT *
FROM cte n
JOIN categories c ON c.id = n.category_id
WHERE c.priority > 1
AND rnk <= 3;

Demo

How can I get 5 records for each 'category' with one select in MySQL?

This SQL script for mysql will give you the first five of all cats.

SELECT * FROM your_table WHERE ctg_topic_id IN (SELECT DISTINCT ctg_topic_id from your_table) GROUP BY ctg_topc_id LIMIT 0,5

Via PHP. (i omit the PHP connection code)

First, get the list;

$stm = $pdo->preprare("SELECT DISTINCT cth_topc_id FROM my_table");
$stm->execute():
$cats = $stm->fetchAll();
$result = array();
foreach ($cats as $cat) {
$query = $pdo->prepare("SELECT * my_table WHERE cth_topic_id=? LIMIT 0,5");
$query->bindParams(1,$cat);
$pdo->execute();
$result[$i] = $query->fetchAll();
}

This simplified code should produce a matrix containing N arrays (where N is the number of distinct cats) that contains an array of 5 elements of the result set.

I've not checked the code so any hints will be corrected thanks.

One problem is that LIMIT 0,5 will get the first 5 items, withot any logic. If you want to sort use ORDER BY for example by ('date') etc.

---

Related Topics