Selecting multiple "groups" of records in one MySQL Query

[EricMartello]

Lets say I have 100 records of sunglasses and within those records are 10 distinct frame colors. Is it possible to write a single query that will select UP TO 3 of each color out of the list, returning a result equal to 50, or below 50 if there quantities do not exist with the chosen grouping?

Example:

id - color_id
-----------------
1 - 1
2 - 1
3 - 1
4 - 1
5 - 2
6 - 2
7 - 2
8 - 3
9 - 3
10 - 4

Desired Result:

id
---------------
1
2
3
5
6
7
8
9
10

 

[KLin]

SELECT TOP 3 id from table where colord_id = 1
UNION ALL
SELECT TOP 3 id from table where colord_id = 2
UNION ALL
SELECT TOP 3 id from table where colord_id = 3
UNION ALL
SELECT TOP 3 id from table where colord_id = 4

that's one way to do it. Another way is to do some sort of sql procedure/code procedure that gets a list of color_ids, then runs a loop through each color_id inserting into another table the top 3 id's of each color_id.

 

[GeekDrew]

Try this code, and see if it does what you're looking for.

-- create the example table

CREATE TABLE IF NOT EXISTS `frames` (
`key_id` int(11) NOT NULL default '0',
`color_id` int(11) NOT NULL default '0',
PRIMARY KEY (`key_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- insert the provided example data
INSERT INTO `frames` (`key_id`, `color_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 2),
(6, 2),
(7, 2),
(8, 3),
(9, 3),
(10, 4);

-- run a query to get the provided result set
SET @num = 1;
SET @color_id = '';

SELECT t1.key_id
FROM (
SELECT
key_id,
color_id,
@num := IF (@color_id = color_id, @num + 1, 1) AS row_number,
@color_id := color_id AS dummy
FROM frames
) AS t1
WHERE t1.row_number <= 3
ORDER BY t1.key_id ASC
LIMIT 50;

When I run those queries, the result returned matches your above desired result:

mysql> SET @num = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @color_id = '';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT t1.key_id
-> FROM (
-> SELECT
-> key_id,
-> color_id,
-> @num := IF (@color_id = color_id, @num + 1, 1) AS row_number,
-> @color_id := color_id AS dummy
-> FROM frames
-> ) AS t1
-> WHERE t1.row_number <= 3
-> ORDER BY t1.key_id ASC
-> LIMIT 50;
+--------+
| key_id |
+--------+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+--------+
9 rows in set (0.00 sec)

Oh, and this is likely inefficient as hell.

 

[EricMartello]

Hehe I should have mentioned, I'm pulling data from millions of records so the query needs to be well-written because I can't be having 30-60 min query times. I have been looking at options using GROUP BY and HAVING but neither fit the bill. I also found this:

http://www.artfulsoftware.com/...eries.php?&bw=1920#104

Its not exactly what I'm looking for and I couldn't transpose that query to work the way I need it to. This might be another one I have to manually "decode" by writing some function...just load all records into an array then spit out n of each.

 

[GeekDrew]

That being the case... my advice is to try my suggestion once, and see how long it takes. If it's not acceptable, look more... but I can't think of any other way to do this off of the top of my head.