Limiting

[sks]

Hi,

I have a products table which apart from other things has a column called
manufacturer. I want to run a query that returns x number of products PER
manufacturer for each manufacturer that matches some criteria.
table::products
--------
id
name
cost
stock
manufacturer
I can only figure out how to limit it to 1, eg

"select manufacturer, name from products where manufacturer like 'A%' group
by manufacturer order by manufacturer, name"

which will return something like this

ManufacturerX, ProductA
ManufacturerY, ProductB
ManufacturerZ, ProductC

What I'd like to do is have something like this returned:

ManufacturerX, ProductA
ManufacturerX, ProductB
ManufacturerX, ProductC
ManufacturerY, ProductD
ManufacturerY, ProductE
ManufacturerY, ProductF
ManufacturerZ, ProductG
ManufacturerZ, ProductH
ManufacturerZ, ProductI

Depending on what limit I set per manufacturer.

Hope that makes some sense !

[Bill Karwin]

sks wrote:

I want to run a query that returns x number of products PER
manufacturer for each manufacturer that matches some criteria.

I thought about this for a few minutes, and no "pretty" single-query
solution comes to mind.

You can always do it in multiple queries:
1. Fetch list of distinct manufacturer names
2. Loop over list of manufacturers
3. SELECT manufacturer, name FROM products
WHERE manufacturer = ? ORDER BY name LIMIT 3
4. Concatenate the results of all these queries together in your
application, and then output.

Another method would be to do build up a UNION query, if you are using
MySQL 4.0.x or later.

SELECT manufacturer, name FROM products
WHERE manufacturer = "ManufacturerX" ORDER BY name LIMIT 3
UNION
SELECT manufacturer, name FROM products
WHERE manufacturer = "ManufacturerY" ORDER BY name LIMIT 3
UNION
SELECT manufacturer, name FROM products
WHERE manufacturer = "ManufacturerZ" ORDER BY name LIMIT 3

Regards,
Bill K.