Selecting 2 distinct rows MySQL

Question

I have a table called categories that has 3 columns: id, name, parent_id.

ID         name         group_id
==         ==           ==
1          haim         1
2          gil          2
3          alon         1
4          idan         1
5          shai         3
6          adi          2
7          itay         3
8          aviram       1
9          lioz         3
10         amit         2
11         ben          2
12         eran         1

i need to select 2 rows of each group_id in one query so the result would be:

ID         name         group_id
==         ==           ==
1          haim         1
3          alon         1
2          gil          2
6          adi          2
5          shai         3
7          itay         3

it's very important that it will also be sorted by group_id.

Thanks!

Answer 1

There are probably several ways to acheive this, but I'd do two queries, and use UNION to merge them together.

Something like this:

(SELECT .... WHERE ID=GROUP_ID)
UNION
(SELECT .... GROUP BY GROUP_ID)
ORDER BY ID;

Here's the relevant MySQL Manual page.

Answer 2

I took in consideration the first and second ID found in the table per each group_id.

SELECT ID, NAME, GROUP_ID
FROM MYTABLE WHERE ID IN (
    SELECT MIN(ID)
    FROM MYTABLE
    GROUP BY GROUP_ID)

UNION ALL

SELECT ID, NAME, GROUP_ID
FROM MYTABLE WHERE ID IN (
    SELECT MIN(ID)
    FROM MYTABLE
    WHERE ID NOT IN (
      SELECT MIN(ID)
      FROM MYTABLE
      GROUP BY GROUP_ID
      )
    GROUP BY GROUP_ID)
ORDER BY GROUP_ID

Answer 3

SELECT   id  ,
         name,
         parent_id
FROM     ( SELECT  id      ,
                  name     ,
                  parent_id,
                  CASE
                           WHEN @parent_id = parent_id
                           THEN @rownum   := @rownum + 1
                           ELSE @rownum   := 1
                  END           AS rn,
                  @parent_id :=    parent_id
         FROM     (SELECT 1 as ID, 'haim' as NAME, 1 as parent_id
                   UNION ALL SELECT 2, 'gil', 2      
                   UNION ALL SELECT 3, 'alon', 1
                   UNION ALL SELECT 4, 'idan', 1
                   UNION ALL SELECT 5, 'shai', 3
                   UNION ALL SELECT 6, 'adi', 2
                   UNION ALL SELECT 7, 'itay', 3
                   UNION ALL SELECT 8, 'aviram', 1
                   UNION ALL SELECT 9, 'lioz', 3
                   UNION ALL SELECT 10, 'amit', 2
                   UNION ALL SELECT 11,  'ben', 2
                   UNION ALL SELECT 12, 'eran', 1
                  )
                  YourTable
                  JOIN
                           (SELECT @rownum    := 0,
                                   @parent_id := NULL) r
         ORDER BY parent_id
         )
         x
WHERE    rn<=2

Answer 4

Hi,

The most adaptative method would be to consider variables:

set @rank:=0;
set @prev:=0;
select ID,name,group_id from
  ( select ID,name,group_id,@rank:=if(group_id=@prev,@rank+1,1) as rank,@prev:=group_id as dump from 
    ( select ID,name,group_id from categories order by group_id asc) A
  ) A where rank<=2;

This way, you are no longer dependant of the number of row you need to show per group_id.

The method is :

• In the first subquery, you sort rows by the field you group your data
• In the second, you calculate the rank of each row in the group
• In the last one you select rows with rank less than what you need.

Cheers

Guillaume