Selection of 2 different MySQL rows

Question

I have a table called category with three 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_idin one query so that the result is:

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

It is very important that it is also sorted using group_id.

Thank!

Answer 1

I took into account the first and second identifiers found in the table for 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 2

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 3

:

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;

, , group_id.

:

• , .
• , .

Answer 4

There are probably several ways to achieve this, but I would make two queries and use UNION to combine them.

Something like that:

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

Here is the relevant MySQL manual page .