Get previous day inserted data from table using MYSQL-CodePudding

I want to get data of previous day which inserted in table

SELECT * 
FROM tbl_stockpricemaster AS sm 
WHERE DATE(sm.inserted_on)=DATE(NOW()- INTERVAL 1 DAY) 
  AND sm.stock_keyvalue='positive' 
GROUP BY sm.stock_id 
ORDER BY sm.stock_difprice DESC 
LIMIT 5;

This is my query but the problem is that when there is no data inserted on previous day then it show blank but i want to show the last inserted record

CodePudding user response：

You will have to find out which date is the previous date

This can be done using this statement:

SELECT MAX(inserted_on) FROM tbl_stockpricemaster WHERE inserted_on < DATE(NOW())

So, changing your query to this should solve your problem:

SELECT * 
FROM tbl_stockpricemaster AS sm 
WHERE DATE(sm.inserted_on)=(SELECT MAX(inserted_on) 
                            FROM tbl_stockpricemaster 
                            WHERE inserted_on < DATE(NOW()) )
  AND sm.stock_keyvalue='positive' 
GROUP BY sm.stock_id 
ORDER BY sm.stock_difprice DESC 
LIMIT 5;