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!
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
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
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
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.
精彩评论