MYSQL join and group by five field per store-CodePudding

I'm trying to get a complex (at least to me) MySQL query, and I can't get the result that I want.

I have one DB with 2 tables:

stores

id, name

products

id, store_id, hardware, name, price, url, notify

I want to get 3 products per store_id with notify = 1, hardware = 'gpu' and order by price asc.

So if I launch this SQL sentence:

SELECT s.name as store, p.id as id, p.name as name, p.hardware as hw, p.price as price, p.url as url, p.notify as notify
FROM products p JOIN stores s
ON p.store_id = s.id
AND p.hardware='gpu'
AND p.notify = 1
GROUP BY store
ORDER BY p.price asc;

I only get one product per store_id, and I'd like to get (maximum) tree elements per store_id. Something like next example:

store	id	name	hw	price	url	notify
store1	1	name1	gpu	price	url	1
store1	2	name2	gpu	price	url	1
store1	3	name3	gpu	price	url	1
store2	8	name8	gpu	price	url	1
store2	12	name12	gpu	price	url	1
store3	22	name22	gpu	price	url	1

And so on...

How can I do that?

Example data

stores

CREATE TABLE `stores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(50) NOT NULL,
  `afiliate` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

products

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `store_id` int(11) NOT NULL,
  `hardware` char(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `notify` tinyint(4) NOT NULL
  PRIMARY KEY (`id`),
  KEY `Store_id_to_products` (`store_id`),
  CONSTRAINT `Store_id_to_products` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1961 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

select id, name from stores;

result

id	name
1	Amazon
2	Ebay
3	BestBuy
4	Target

select * from products;

result

id	store_id	hardware	name	price	notify
1	3	gpu	EVGA GeForce RTX 3070 FTW3 Ultra Gaming 8GB GDDR6	949.00	1
2	3	gpu	Zotac Gaming GeForce RTX 3090 Trinity OC 24GB GDDR6X	2259.00	1
3	3	gpu	EVGA GeForce RTX 3090 FTW3 Ultra Gaming 24GB GDDR6X	2749.00	1
4	3	gpu	Asus TUF Gaming GeForce RTX 3090 OC Edition 24GB GDDR6X	2549.00	1
5	1	gpu	Gigabyte GTX 1660 Super OC 6G	489.00	1
6	1	gpu	Asus TUF GTX 1660 Super OC 6GB	428.99	1
8	1	gpu	GIGABYTE RTX 3060 GAMING OC 12GB	689.00	1
9	1	gpu	Gigabyte RTX 3080 Aorus Master 10GB	1398.99	1
10	2	gpu	Asus ROG Strix RTX 3080 OC 10GB Blanca	1312.00	1
11	2	gpu	Gigabyte RTX 3080 Gaming OC 10GB	1258.99	1
12	2	gpu	Asus ROG Strix RTX 3090 OC Gaming 24GB	2599.00	1
13	4	gpu	Asus TUF Gaming Radeon RX 6800 OC 16GB	1078.99	1
14	4	gpu	GIGABYTE RX6800XT GAMING OC 16GB	450.00	1
15	4	gpu	Gigabyte GeForce GTX 1660 Ti OC 6G	499.95	1
16	4	gpu	ZOTAC GeForce GTX 1660 SUPER Twin Fan	529.95	1

I'm trying to do a SQL sentence that get 3 cheapest products per store. This is the result that I want:

id	store_id	hardware	name	price	notify
1	3	gpu	EVGA GeForce RTX 3070 FTW3 Ultra Gaming 8GB GDDR6	949.00	1
2	3	gpu	Zotac Gaming GeForce RTX 3090 Trinity OC 24GB GDDR6X	2259.00	1
4	3	gpu	Asus TUF Gaming GeForce RTX 3090 OC Edition 24GB GDDR6X	2549.00	1
5	1	gpu	Gigabyte GTX 1660 Super OC 6G	489.00	1
6	1	gpu	Asus TUF GTX 1660 Super OC 6GB	428.99	1
8	1	gpu	GIGABYTE RTX 3060 GAMING OC 12GB	689.00	1
10	2	gpu	Asus ROG Strix RTX 3080 OC 10GB Blanca	1312.00	1
11	2	gpu	Gigabyte RTX 3080 Gaming OC 10GB	1258.99	1
12	2	gpu	Asus ROG Strix RTX 3090 OC Gaming 24GB	2599.00	1
14	4	gpu	GIGABYTE RX6800XT GAMING OC 16GB	450.00	1
15	4	gpu	Gigabyte GeForce GTX 1660 Ti OC 6G	499.95	1
16	4	gpu	ZOTAC GeForce GTX 1660 SUPER Twin Fan	529.95	1

Thanks in advance!!

CodePudding user response：

If your MySQL version is lower than 8 you could use :

  SELECT
            id,
            store_id,
            hardware,
            price,
            notify
    FROM
          (
            SELECT
                   id,
                   store_id,
                   hardware,
                   notify,
                   price,
                   @rn := IF(@prev = store_id, @rn   1, 1) AS rn,
                   @prev := store_id
            FROM products
           JOIN ( SELECT @prev := NULL, @rn := 0) AS vars
           where hardware='gpu' and notify=1
           ORDER BY price ASC
           ) AS T1
    WHERE rn <= 5
   ORDER BY id asc;

Demo

In MySQL 8

WITH my_table  AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY  price ASC ) AS rownum
  FROM products
)
SELECT * FROM my_table WHERE rownum <= 5
order by id asc;

Demo