Get Last Record In Each MySQL Group

I need to this SQL query to get last record in each group:

PHP Code:

SELECT id, category_id, post_title
FROM posts
WHERE id IN (
    SELECT MAX(id)
    FROM posts
    GROUP BY category_id
); 


how do can I work with query builder to get last record in each group in codeigniter 4?

Use Subqueries method. ie:

PHP Code:

        $builder = $this->db->table('posts');
        $builder->select('id, category_id, post_title');
        $builder->whereIn('id', function(BaseBuilder $builder) {
            return $builder->select('MAX(id)', false)->from('posts')->groupBy('category_id');
        });
        $query = $builder->get()->getResult();
        return $query; 


remember, you need to add in top of your model:

Code:

use CodeIgniter\Database\BaseBuilder;