List:General Discussion« Previous MessageNext Message »
From:André Hänsel Date:August 13 2006 7:29pm
Subject:Finding the last row that has
View as plain text  
Hi,

I have one table with images (image_id, name, filename, vote_count) and one
table with votes (vote_id, image_id, vote_value, user_id).

I want to find the image with the lowest vote_count that a known user has
not yet voted.

For MySQL 4.0 I have the following:
SELECT i.* FROM images i LEFT JOIN votes v ON i.image_id = v.image_id WHERE
v.id IS NULL OR v.user_id != '1234' GROUP BY image_id ORDER BY vote_count
ASC LIMIT 1;

Now my three questions:

Is this the optimal query?
Is there a better query when using MySQL 4.1?
Since it's redundant, can I get rid of the vote_count column?

Regards,
André

Thread
Finding the last row that hasAndré Hänsel13 Aug
  • Re: Finding the last row that hasChris14 Aug