CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
S.ov
S.ov

Reputation: 410

statement to query table but limit returned rows based on distinct column

I'm curious if a single SQL statement can do something like this?

I have a table of of something like this:

name, manufacturer, value

I'd like to list the top x number of each manufacturer based on a query of other criteria.

For example,

SELECT name, manufacturer, value 
from table 
where value<100 
order by manufacturer

But ideally I'd like, say, to only return the first 10 from each manufacturer. I am aware I can write a script to parse a returned table and ignore records after the first 10, but is there a way to build a query that would do it automatically?

Upvotes: 2

Views: 228

Answers (2)

GarethD
GarethD

Reputation: 69759

I think this would work for you:

SET @Manufacturer = '';
SET @RowNum = 1;

SELECT  Name,
        Manufacturer,
        Value
FROM    (   SELECT  @RowNum := IF(@Manufacturer = Manufacturer, @RowNum + 1, 1) AS RowNumber,
                    Manufacturer,
                    Name,
                    Value,
                    @Manufacturer := Manufacturer
            FROM    Item
            ORDER BY Manufacturer, Name DESC
        ) Item
 WHERE  RowNumber <= 10

This returns the first 10 ordered by name, you would need to change the order by clause in the subquery to change how the query decides which are the "first" 10 records for each manufacturer.

Upvotes: 2

Teja
Teja

Reputation: 13534

SELECT name,manufacturer,value
FROM YOURTABLE 
LIMIT 0,x;

Upvotes: 0

Related Questions