use group_by in codeigniter to get latest data

Tags: , , ,



I am new to CodeIgniter. I have seen similar threads related my question, however I am unable find an exact solution. I have a table ‘request’ which contains two columns, one with ‘userId’ the other one with ‘requestTime’ as shown below (for example).

key userId requestTime
6 abc 55555
5 abc 44444
4 xyz 33333
3 abc 22222
2 xyz 11111
1 lmn 00000

Here I would like to get a list of ‘userId’ with latest time (maximum of ‘requestTime’) as below.

userId requestTime
abc 55555
xyz 33333
lmn 00000

I am using the following code in CodeIgniter.

 $this->db->order_by('request.requestTime', 'desc');
 $this->db->group_by('request.userId');
 return $this->db->get('request')->result_array();

The above CI code does not provide results as I expected. How can get the expected result.

Answer

$this->db->select_max('request.requestTime','aliasName');
$this->db->group_by('request.userId');
return $this->db->get('request')->result_array();

I have updated the answer.. hope it will help others as well.



Source: stackoverflow