1

I have a model which is dd1. The model is including several type of model name. I want to fetch last data of model by refer to their last_datecreated for every model and every model name. 

    $r= mysql_query("SELECT a.model_no, a.datecreated, a.model_name, a.model
                     FROM tbl_dsp_mail_inbox a 
                     INNER JOIN (SELECT model_name, MAX(datecreated) AS datecreated
                     FROM tbl_dsp_mail_inbox a 
                     WHERE status = 'sent' 
                     GROUP BY model_name
                     ) AS b ON a.model_name = b.model_name AND a.datecreated, b.datecreated");
     if(mysql_num_rows($r) !== 0){
         while ($data = mysql_fetch_object($r)){
         $model_n = $data->model_name;
         $datecreated = $data->datecreated; 
         $model_no = $data->model_no;

}

model  | model_name | model no | last_datecreated |
----------------------------------------------------
dd1    | toyota     | 0012-12-B| 25/12/2014
dd1    | kancil     | 0013-12-B| 25/12/2014
dd1    | toyota     | 0011-12-C| 24/12/2014
dd1    | toyota     | 018-13-C | 03/11/2014
dd1    | kancil     | 0014-12-E| 01/11/2014

But when I run this code the result is :

model | model_name | model_no | last_datecreated
--------------------------------------------------
dd1   | toyota     | 018-13-C | 25/12/2014
dd1   | kancil     | 0013-12-B| 25/12/2014

The should be like this (desired output)

model | model_name | model_no | last_datecreated
--------------------------------------------------
dd1   | toyota     | 0012-12-B | 25/12/2014
dd1   | kancil     | 0013-12-B | 25/12/2014
den
  • 37
  • 5

1 Answers1

0

Try this: 

SELECT a.model_no, a.datecreated, a.model_name, a.model
FROM tbl_dsp_mail_inbox a 
INNER JOIN (SELECT model_name, MAX(datecreated) AS datecreated
            FROM tbl_dsp_mail_inbox a 
            WHERE status = 'sent' 
            GROUP BY model_name
           ) AS b ON a.model_name = b.model_name AND a.datecreated = b.datecreated;
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • why we just select model_name and datecreated for inner join? pls advise :) – den Jan 02 '15 at 07:52
  • I already updated my code above but when I run the code it display no result. Pls check my code is there any error – den Jan 02 '15 at 08:03
  • still no result display :( – den Jan 02 '15 at 08:43
  • ok I got it! I added MAX(model_no) AS model_no at inner join query so it workings. Thanks for your help! :) – den Jan 02 '15 at 08:50