[SOLVED] CodeIgniter SELECT query to return qualifying rows with their highest value in a specific column

CodeIgniter SELECT query to return qualifying rows with their highest value in a specific column

This is my SQL table.

marks_table
ID  STUD_ID MARKS   VERSION    VERIFICATION_ID
1      50     90       1             2
2      22     50       1             2
3      33     20       1             2
4      10     30       1             2
5      55     50       1             2
6      55     40       2             2
7      20     60       1             2
8      30     90       1             2
9      10     88       1             3
10     10     45       2             3

I want to get all the results by verification_id with the greatest version per stud_id. For example ID 5,6 and 9,10 have same stud_id with different marks and there version is also different. I want to get max version result and all other result from that verification_id.

In CodeIgniter, I have used the following methods:

$this->db->select('*');
$this->db->from('marks_table');
$this->db->where('version IN (SELECT MAX(version) FROM marks_table)', NULL, FALSE);
$this->db->where('verification_id', '2');
$this->db->get();

What I got is only the final max version.

marks_table
ID  STUD_ID MARKS   VERSION    VERIFICATION_ID
6      55     40       2             2

What I really want:

marks_table
ID  STUD_ID MARKS   VERSION    VERIFICATION_ID
1      50     90       1             2
2      22     50       1             2
3      33     20       1             2
4      10     30       1             2
6      55     40       2             2
7      20     60       1             2
8      30     90       1             2

Solution

First find the Max version then fetch it's data:

   select * from marks_table a
    where version = (select max(version) from 
                        marks_table where stud_id = a.stud_id);