Question 22028 in Mysql Question posted in
The official MySQL documentation can be found here.

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

"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

Tags:

3

Answers


  1. 0

    To return only one row use LIMIT 1:

    SELECT * FROM tailors
       WHERE id IN(
        SELECT min(id)
        FROM tailors
        GROUP BY cat_id,id,name,status
      ) LIMIT 1
    Login or Signup to reply.
  2. 0

    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
             );
    Login or Signup to reply.
  3. 0

    If the logic remains same throughout the table, and the version of the DB is 8.0+, then use such an aggregation :

    SELECT name
  FROM( SELECT t.*, MIN(id) OVER (PARTITION BY cat_id) AS min
          FROM tailors AS t ) AS tt
 WHERE id = min

    assuming id is a primary key column, there will be only one minimum value per each cat_id.

    GROUP BY cat_id is handled by PARTITION BY cat_id and the other columns(name and status) following it should be removed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search