SQL Count elements based on newest 150 entries in table

Question

I'm trying to change two SQL queries I have right now so that in addition to giving me a result from a table, it only gives me that result based on the newest say 150 entries within the table.

My two queries at the moment are:

SELECT COUNT(*)
FROM `stock`.`replacements.archive`
WHERE `ciref` = '$cihistory'

and

SELECT `req_model`, COUNT(`req_model`) AS `value_occurrence`
FROM `stock`.`replacements.archive`
GROUP BY `req_model`
ORDER BY `value_occurrence` DESC LIMIT 5

Is there a way for me to achieve this? The id field in the table is used as normal so guessing would use this but im not sure how to achieve it

The aim here is to only do the counting of the variable based on the latest 150 entries in the table, i dont want to count everything in the table.

Anyone has any ideas it would be much appreciated!

Thanks

Answer 1

assuming you have an autoincremnt id the you could select the newest 150 entry as

    SELECT * 
    FROM `stock`.`replacements.archive` 
    ORDER BY id limit 150 

and then perform the query on this subquery as

    SELECT `req_model`, COUNT(`req_model`) AS `value_occurrence` 
    FROM (  
        SELECT * 
        FROM `stock`.`replacements.archive` 
        ORDER BY id DESC  limit 150 
    ) t
    GROUP BY `req_model` 
    ORDER BY `value_occurrence`