Showing most recent records in mysql database using group by

[john278]

Hi,

I am trying to use this to show records:

SELECT COUNT(b_id), b_id, name, item_cost, item_c_pc, date, qty_pk FROM stockcostm WHERE app=‘1’ GROUP BY b_id ORDER BY date DESC

The only issue is it orders the results by date, which is great and which I would prefer, but it’s not bringing up the rows with the most recent dates by group b_id.

How can I resolve this. I’ve tried researching high and low, but with nothing.

I have rows like

b_id, name, date, item_cost
B02 Dave 2015/09/21 2.99
B05 Kim 2015/08/12 3.44
B02 Dave 2018/09/14 5.45
B06 Mark 2016/09/14 1.99

I want it to return it as follows:
B02 Dave 2018/09/14 5.45
B06 Mark 2016/09/14 1.99
B05 Kim 2015/08/12 3.44

Thanks

[r937]

SELECT t.b_id
     , t.name
     , t.item_cost
     , t.item_c_pc
     , t.`date`
     , t.qty_pk 
     , c.stock_count
  FROM ( SELECT b_id                                        
              , COUNT(*) AS stock_count                  
              , MAX(`date`) AS latest_date                  
           FROM stockcostm                                  
          WHERE app = 1                                     
         GROUP                                              
             BY b_id ) AS c  
INNER
  JOIN stockcostm AS t
    ON t.b_id = c.b_id
   AND t.`date` = c.latest_date
ORDER 
    BY t.`date` DESC

[john278]

Thanks. It works just fine.

Much appreciated.

[benanamen]

Just a side note, you are storing your dates in an incorrect format. It should be a date column in the format YYYY-MM-DD

[john278]

The dates are stored like you said, I just quickly gave an example how I wanted it outputted above with the order or the items. the dates I gave in the example is not the date format I have in my database. But thanks for the suggestion.