SQL, search only one element per category

Question

I am trying to make an SQL query that selects only one element per cat_id, from the genus of a cycle For where it checks if there is already a cat_id=1 and if you already have it goes forward until you run the whole table.

I am using this function to fetch all files

public function get_products()
    {
        $sql  = "SELECT * FROM products";
        $query = $this->db->query($sql); 
        return $query->result_array();
    }

and I just want to get 1 per cat_id

Answer 1

If I understand correctly, you want to return your categories and only 1 record each. This you can achieve by making a base query that returns the last registered ID to a category

SELECT MAX(id) FROM products GROUP BY cat_id

If your need is to return the first product per category, just change the MAX per MIN.

Based on this query, you can return the information this way:

SELECT * FROM products WHERE id in (SELECT MAX(id) FROM products GROUP BY cat_id)