select query question

blackhorse

table is like this

product_id, file_name, rank

1, cat1.jpg, 1
1, cat2.jpg, 2
2, dog1.jpg, 1
2, dog2.jpg, 2
2, dog3.jpg, 3

I want select one image for each product, and the select order is by rank.

so basically, i want the results to be

product_id, file_name, rank
1, cat1.jpg, 1
2, dog1/jpg, 1

How can I do this in one query? I used to use the loop of queries to do that, but I want to use better query now.

I am on mysql 5, but if the query will be mysql 4 compatible too, that will be great.

Thanks!

sheephat

SELECT * FROM tablename ORDER BY product_id WHERE rank=1

I think thats right, and i think that is what your looking for if not, feel free to correct

blackhorse

I didn't make myself clear. The Rank is free set up values.

So it could be

product_id, file_name, rank

1, cat1.jpg, 11
1, cat2.jpg, 12
2, dog1.jpg, 8
2, dog2.jpg, 9
2, dog3.jpg, 10

So the results will be

1, cat1.jpg, 11
2, dog1.jpg, 8

Basically, I want to select 1 image for each product such as for product id 1 and 2. And due to product id 1 or 2 both have multiple images, the image I will select is the top ranked image for each product. The rank value could be free input value.

Thanks!

sheephat

I understand now, im going to go see if i can get something to work, i think there is an error in the way you have designed that table.

I will work on a query i have just thougt up and send it back if it works

blackhorse

the table design is

images_id, product_id, file_name, rank

1, 1, cat1.jpg, 11
2, 1, cat2.jpg, 12
3, 2, dog1.jpg, 8
4, 2, dog2.jpg, 9
5, 2, dog3.jpg, 10

Becasue image_id has nothing to do with this sql, I didn't bring that field in my sample.

Due to I don't have large number records before. What I did before is get the unqiue product ids first. And loop for each product id, assign as $this_product_id

select * from product_images where product_id=$this_product_id order by rank limit 0, 1

but if the number of products incorease, such as for 100 products, it will have to do 100 times sql.

So I am looking for an efficient sql solution here now.

Thanks!

Roger_Ramjet

This is where you use a GROUP BY

$query = "SELECT image_id, product_id, file_name, MIN(rank) FROM product_images
GROUP BY product_id";

That will return 1 row for each product as you want

blackhorse

Thanks! what about

images_id, product_id, file_name, rank

1, 1, cat1.jpg, 11
2, 1, cat2.jpg, 12
3, 2, dog1.jpg, 8
4, 2, dog2.jpg, 8
5, 2, dog3.jpg, 10

What if multiple records has the same min(rank) value, such as in the new sample, image 3, and 4? In that case, I want to select by the file name in DESC order. So the results I want should be

1, 1, cat1.jpg, 11
4, 2, dog2.jpg, 8

How should I do this?

Thanks again.