Wsrtjtyk

I have select statement as below and sample output :-

select uph.creation_date, p.name,p.product_id from product p 

                left join user_product_history uph on p.product_id = uph.product_id 

                where uph.user_id = 124 order by uph.creation_date desc 

How can I group by product ID with lastest creation date? Please help. Thank you.

Edited with PHP API .model file

// ~/user/product_history

    public function product_history($data) {

        $sql = 'select uph.creation_date,

        p.name,

        p.product_id

        from product p

        join user_product_history uph

        on p.product_id = uph.product_id and

        uph.user_id = ?

        join (select product_id,

              MAX(creation_date) AS max_creation_date

              from user_product_history

              where user_id = ?

              group by product_id) dt

        on dt.product_id = uph.product_id and

        dt.max_creation_date = uph.creation_date

        order by uph.creation_date desc';



        $result = $this->db->query($sql, array($data['user_id']));

        $records = array();

        foreach( $result->result_array() as $r ) {

            $r['product_id'] = (int) $r['product_id'];

            $r['sub_category_id'] = (int) $r['sub_category_id'];

            $r['merchant_id'] = (int) $r['merchant_id'];

            if (!isset($r['price_discount'])) $r['price_discount'] = '';

            $records = $r;

        }

        return $records;

    }

• Firstly, you dont need a Left Join here, as you are filtering on user_product_history table also. It does seem like you want to show only those product(s), which has a creation_date corresponding to user_id = 124. So, you can simply use Inner Join instead.


• In a derived table (sub-select query), determine the maximum value of creation_date for every product_id.


• Now, use this result-set to join to the main tables, on product_id and the creation_date, to get the complete row.

Try the following:

select uph.creation_date, 

       p.name,

       p.product_id 

from product p 

join user_product_history uph 

  on p.product_id = uph.product_id and 

     uph.user_id = 124 

join (select product_id, 

             MAX(creation_date) AS max_creation_date

      from user_product_history 

      where user_id = 124 

      group by product_id) dt 

  on dt.product_id = uph.product_id and 

     dt.max_creation_date = uph.creation_date 

order by uph.creation_date desc

select 

    uph.creation_date, 

    p.name,p.product_id 

from 

    product p 

    left join user_product_history uph on p.product_id = uph.product_id 

where 

    uph.user_id = 124 and

    uph.creation_date = (select

                             max(creation_date)

                         from 

                             user_product_history) 

order by 

    uph.creation_date desc