Limit A Particular Column

[dweb]

Hi

 

I have the following code

 

$result = mysql_query("SELECT * FROM products");
while($row = mysql_fetch_array($result))
{
echo $row['name'];
}

 

and the following DB table

 

id,type,name

1,1,Mouse

2,1,Printer

3,1,Keyboard

4,2,Laptop

5,2,Desktop

6,2,Tablet

7,2,Mp3

 

As you can see, the columns are `ID`, `Type` and `Name` and at the moment it comes back like

 

Mouse

Printer

Keyboard

Laptop

Desktop

Tablet

Mp3

 

My database also has lots of other products in it, that is just a snippet

 

At the moment it returns all the products, but what I want to do is limit it to that it only returns 2 rows from each type (2nd column), so it would look like

 

Mouse

Printer

Laptop

Desktop

 

Can anyone help? I've tried LIMIT but cannot see how to do this

 

thanks

Edited October 28, 2012 by dweb

[Jessica]

You will want to use the having clause with a group by and probably order too, and left join the table to itself.

[Barand]

try this

 

SELECT id, name
FROM (
   SELECT id, name,
   @row := IF(type=@prev, @row+1, 1) as row,
   @prev := type as type
   FROM products
   ) x
   JOIN (SELECT @row := 0, @prev:=0) as y
WHERE row < 3;

[jcbones]

Don't ask me why or how it works...

$sql = "SELECT type, name
FROM products
WHERE (
SELECT COUNT(id) FROM products AS p
WHERE p.type = products.type AND p.id < products.id
) < 2"; //change the last number (currently 2) to the number of desired results per group.

[Barand]

Though as it queries the table for each row the run time will expand exponentially as the file grows

[dweb]

thanks everyone, jcbones, your example seemed to be the quickest, but thanks everyone for all the help

[Barand]

Mine required a minor amendment - ORDER BY TYPE added

 

SELECT id, name
FROM (
       SELECT id, name,
       @row := IF(type=@prev, @row+1, 1) as row,
       @prev := type as type
       FROM products
       ORDER BY type
       ) x
       JOIN (SELECT @row := 0, @prev:=0) as y
WHERE row < 3;

 

On a table with 182,000+ records it took 0.2 seconds.

JCBones version is still running.

[dweb]

Actually it seems to have an issue running and stops certain rows from being loaded, i'll try a few other things

[dweb]

Mine required a minor amendment - ORDER BY TYPE added

 

SELECT id, name
FROM (
SELECT id, name,
@row := IF(type=@prev, @row+1, 1) as row,
@prev := type as type
FROM products
ORDER BY type
) x
JOIN (SELECT @row := 0, @prev:=0) as y
WHERE row < 3;

 

On a table with 182,000+ records it took 0.2 seconds.

JCBones version is still running.

for some reason it returns every record, do you know why that might be?

[Barand]

for some reason it returns every record, do you know why that might be?

 

Did you add the ORDER BY type?

[Barand]

try rearranged version

 

SELECT id, name
FROM (
               SELECT id, name,
               @row := IF(type=@prev, @row+1, 1) as row,
               @prev := type as type
               FROM products
                 JOIN (SELECT @row := 0, @prev := 0) as y
               ORDER BY type
               ) x                
WHERE row < 3;

[White_Lily]

"SELECT id, name FROM products WHERE type = 1 OR type = 2 LIMIT 2";

 

 

Try that?

[Jessica]

That will only give two total results.

 

"SELECT id, name FROM products WHERE type = 1 OR type = 2 LIMIT 2";

 

 

Try that?

[jcbones]

Barands version is a much better way, as it uses only one pass.

 

Try this one: Make sure you put an index on 'type'.

SELECT id, name, @num := if( @type =
type , @num +1, 1 ) AS row_number, @type :=
type AS dummy
FROM products
FORCE INDEX (
type )
GROUP BY type, name
HAVING row_number <=2

 

I do believe this is a one pass solution, so it should be the fastest. Hoping Barand will approve.