Sorting by date, only with the newest row for each product

To create a table, I am retrieving the data with the following query:

SELECT cId,cProductId,cDate,cInfo FROM tProduct ORDER BY cProductId ASC,cDate ASC;

Naturally I simplified the query a lot, but you get the notably, there might be many record for a same product, and the only one that interest me in the newest, thus how I order my query: I only store the information for the last row I encounter in my while loop.

while (($row = $result->fetch()) !== FALSE)
{
      if(!empty($row[0])){
          if(($table_row[1]!=$row[1])&&(!empty($table_row[0]))){
             开发者_如何转开发 //Display the last row saved
          }
          $table_row= array($row[0],$row[1],$row[2],$row[3]);
      }
}

Now I want to implement some sorting options for the html table which is generated. I'll store the sorting information in a session variable. Amongst the sorting option, I want to be able to sort by date. How can I be sure to retrieve only the newest row for each product while ordering my query by date?

You didn't include FROM clause in your query. However, assuming you want the newest row per product ID, you can do something like this:

SELECT cId,cProductId,cDate,cInfo
FROM tProduct t1
WHERE cDate = (SELECT max(cDate) FROM tProduct t2 where t1.cProductID = t2.cProductId)
ORDER BY cProductId ASC,cDate ASC;