Search code examples
sqlmysqlgreatest-n-per-group

What SQL query can I use to get the first record of each group based on a certain column?

"I'm working with a database table in SQL and I want to retrieve the first row of each group based on a certain column. The table has columns 'group_id' and 'value', and I want to retrieve the row with the lowest 'value' for each unique 'group_id'. How can I achieve this using SQL?"

table

based on the example table above i would like to get just the name alex and brown

Here is what i have tried

SELECT * FROM tailors
                            WHERE id IN(
                                    SELECT min(id)
                                    FROM tailors
                                    GROUP BY cat_id,id,name,status
                            )

but i am getting all the record when i am just trying to get the first data of each matching category id

Solution

  • You just need to take out id and name from your group by clause -

    SELECT * FROM tailors
 WHERE id IN (SELECT min(id)
                FROM tailors
               GROUP BY cat_id, status
             );