CODEWITHSUNDEEP
mysqlsql
snh_nl
snh_nl

Reputation: 2955

SQL, Select group by count, on max number of columns

we have the following SQL. Group by product_id and count. Limit 6. Query and example output below.

Now, suppose I would want the the Group by product_id and count. Limit 6. to be based on the last 1000 records in the table? So not the whole table, but only group/count based on the last 1000 records. Can this be done?

I am already using the LIMIT to catch the top 6. But is there a way to LIMIT the number of columns that get counted.grouped to a MAX?

Hope this is clear

thx, Sean

SELECT `t`.`product_id` AS `id`, COUNT(*) AS `cnt` 
FROM `sofreport_viewed_product_index` AS `t` WHERE (t.product_id != '24') 
AND (t.store_id = '1') GROUP BY `t`.`product_id` ORDER BY `cnt` DESC LIMIT 6

id  cnt
9   239
440 179
216 169
10  157
494 126
500 118

Upvotes: 0

Views: 249

Answers (1)

Le-Mr-Ruyk
Le-Mr-Ruyk

Reputation: 179

I think it will work like that,

SELECT `t`.`product_id` AS `id`, COUNT(*) AS `cnt` FROM
`sofreport_viewed_product_index` AS `t` WHERE (t.product_id != '24')
 AND (t.store_id = '1') and t.product_id in
(SELECT id FROM sofreport_viewed_product_index ORDER BY ‘cnt‘ LIMIT 1000 )
GROUP BY `t`.`product_id` ORDER BY `cnt` DESC LIMIT 6

Upvotes: 0

Related Questions