2

"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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Abdallah
  • 198
  • 16

3 Answers3

3

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
2

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
             );
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
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
DreamBold
  • 2,727
  • 1
  • 9
  • 24