How to display the last record added in the group by

Question

I am using Codeigniter, I have two tables. From the first table bank_id, I am sending in the second table f_bankid.

1) tbl_bankdata

bank_id | b_bankname | lead_id
1       | 1          | 197
2       | 2          | 197
3       | 3          | 197
4       | 1          | 196
5       | 2          | 196
6       | 3          | 196
7       | 1          | 196
8       | 2          | 196

2) tbl_fileStatus

f_id | f_date      | f_remark        |f_bankid  | f_filestatus| f_dateofadded
1    | 2020-03-29  |This is testing1 |  1       | 6           | 2020-03-28 19:28:50 
2    | 2020-03-30  |This is testing2 |  2       | 6           | 2020-03-28 19:28:50 
3    | 2020-03-30  |This is testing3 |  3       | 6           | 2020-03-28 19:28:50 
4    | 2020-03-30  |Testing for pend |  1       | 3           | 2020-03-29 01:00:04 
5    | 2020-03-29  |Testing1         |  4       | 6           | 2020-03-29 05:22:09 
6    | 2020-03-29  |Testing2         |  5       | 6           | 2020-03-29 05:22:09 
7    | 2020-03-30  |Testing3         |  6       | 6           | 2020-03-29 05:22:09
8    | 2020-03-30  |Testing4         |  7       | 6           | 2020-03-29 05:22:09
9    | 2020-03-30  |Testing5         |  8       | 6           | 2020-03-29 05:22:09 
10   | 2020-03-30  |Testing for OD   |  4       | 4           | 2020-03-29 10:54:46

Now notice in the second table, I have more one same bank_id.

For example

f_id | f_date      | f_remark        |f_bankid  | f_filestatus| f_dateofadded
1    | 2020-03-29  |This is testing1 |  1       | 6           | 2020-03-28 19:28:50 
4    | 2020-03-30  |Testing for pend |  1       | 3           | 2020-03-29 01:00:04 
5    | 2020-03-29  |Testing1         |  4       | 6           | 2020-03-29 05:22:09 
10   | 2020-03-30  |Testing for OD   |  4       | 4           | 2020-03-29 10:54:46

So I have to display the last record added and the output will be as below

4    | 2020-03-30  |Testing for pend |  1       | 3           | 2020-03-29 01:00:04 
10   | 2020-03-30  |Testing for OD   |  4       | 4           | 2020-03-29 10:54:46

I am using below query but not getting my expected output

  $lead_id=$lead->c_id;
  $bank = $this->db->select('*')
               ->from('tbl_bankdata')
               ->join('tbl_fileStatus','tbl_bankdata.bank_id=tbl_fileStatus.f_bankid')
               ->where('tbl_bankdata.lead_id', $lead_id)
               ->group_by('tbl_fileStatus.f_bankid')
               //->order_by('tbl_fileStatus.f_dateofadded','DESC')
               ->get()
               ->result();

My expected output is

  bank_id | b_bankname | lead_id |f_id | f_date      | f_remark        |f_bankid  | f_filestatus| f_dateofadded
    1     | 1          | 197     |4    | 2020-03-29  |Testing for pend | 1        | 3          |2020-03-29 01:00:04
    2     | 2          | 197     |2    | 2020-03-30  |This is testing2 | 2        | 6          |2020-03-28 19:28:50
    3     | 3          | 197     |3    | 2020-03-30  |This is testing3 | 3        | 6          |2020-03-28 19:28:50
    4     | 1          | 196     |10   | 2020-03-30  |Testing for OD   | 4        | 4          |2020-03-29 10:54:46
    5     | 2          | 196     |6    | 2020-03-29  |Testing2         | 5        | 6          |2020-03-29 05:22:09
    6     | 3          | 196     |7    | 2020-03-30  |Testing3         | 6        | 6          |2020-03-29 05:22:09 
    7     | 1          | 196     |8    | 2020-03-30  |Testing4         | 7        | 6          |2020-03-29 05:22:09
    8     | 2          | 196     |9    | 2020-03-30  |Testing5         | 8        | 6          |2020-03-29 05:22:09

And records should remove using the query

f_id | f_date      | f_remark        |f_bankid  | f_filestatus| f_dateofadded
1    | 2020-03-29  |This is testing1 |  1       | 6           | 2020-03-28 19:28:50 
5    | 2020-03-29  |Testing1         |  4       | 6           | 2020-03-29 05:22:09

[This question](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) might help you get the right idea about how to form the SQL. — El_Vanja, Mar 29 '20 at 12:06
@El_Vanja, Yes, I checked that not understood how to use that with Codeigniter. So also I tried msql query. Wait I am updating that code in the question. — user9437856, Mar 29 '20 at 12:09
@El_Vanja, I tried this query SELECT * FROM tbl_bankdata JOIN tbl_fileStatus ON tbl_bankdata.bank_id = tbl_fileStatus.f_bankid — user9437856, Mar 29 '20 at 12:16
@El_Vanja, I have two tables and the question you suggested have only one table — user9437856, Mar 29 '20 at 12:17
You should be able to derive the solution from that question. You need to apply one of the solutions to `tbl_fileStatus` and then just additionally join with `tbl_bankdata` (that table has no effect on selecting the latest record from `tbl_fileStatus`). — El_Vanja, Mar 29 '20 at 12:27
@El_Vanja, I tested and this query is working for me SELECT * FROM tbl_bankdata join tbl_fileStatus on tbl_bankdata.bank_id=tbl_fileStatus.f_bankid WHERE f_id IN ( SELECT MAX(f_id) FROM tbl_fileStatus GROUP BY f_bankid ) — user9437856, Mar 29 '20 at 14:21
Does [this example](https://stackoverflow.com/questions/51960117/codeigniter-subquery-in-query-builder) help? — El_Vanja, Mar 29 '20 at 14:41
Yes, I tried JYelton answer and working but now how can I convert query with Codeigniter? — user9437856, Mar 29 '20 at 15:37
Did you take a look at this second link about writing subqueries in Codeigniter? — El_Vanja, Mar 29 '20 at 15:38
@El_Vanja, Yes, I got my output using both the link. I made below code using both link and it's working — user9437856, Mar 29 '20 at 15:53
Final code here $this->db->select('MAX(tbl_fileStatus.f_id)')->from('tbl_fileStatus')->group_by('tbl_fileStatus.f_bankid'); $subQuery = $this->db->get_compiled_select(); $bank = $this->db->select('*') ->from('tbl_bankdata') ->join('tbl_fileStatus','tbl_bankdata.bank_id=tbl_fileStatus.f_bankid') ->where('tbl_bankdata.lead_id', $lead->c_id) ->where('tbl_fileStatus.f_id IN ('.$subQuery.')', NULL, FALSE) ->order_by('tbl_fileStatus.f_bankid','DESC') ->get() ->result(); — user9437856, Mar 29 '20 at 15:53
I'd suggest you delete that final comment and post it as an answer (it is ok to answer your own question). — El_Vanja, Mar 29 '20 at 16:00
@El_Vanja, Please you can post the answer because you help me to find the answer. — user9437856, Mar 29 '20 at 16:02

How to display the last record added in the group by

0 Answers0