excluding results in a query

[ilmaggy]

Hi,

I've got a query like this:

SELECT user.*, foto.*, votes.sum(rating) FROM votes WHERE votes.foto_id = foto.fid AND user.id = votes.vote_to_user_id GROUP BY votes.foto_id ORDER BY sum(rating) DESC LIMIT 0 , 10

This query selects photos with the highest sum of votes.
Now I want to exclude photos from the same user.id in this top10. (Because users have more than one photo) So what do I have to add to my query to prevent this from happening?
Thanking you in advance,
ruben.

 

[steven290]

couldn't you do a

... GROUP BY votes.foto_id,foto.fid ...

 

[azzazzello]

I think you need to do

... GROUP BY votes.foto_id,user.id ...

 

[ilmaggy]

Unfortunately it didn't work. Still I can see more than one picture of an user in my top10.

This is my exact query:

SELECT user.id AS id, naam, foto.fid AS fid, foto, votes.vote_to_user_id, foto_id, sum( rating ) AS punten FROM votes, foto, user WHERE votes.foto_id = foto.fid AND user.id = votes.vote_to_user_id GROUP BY votes.foto_id, user.id ORDER BY punten DESC LIMIT 0 , 10

 

[azzazzello]

ok, I think I understand what you want...you want top 10 fotos by votes, but if one user has more than 1 foto in top 10, you just want the topmost one, and do not want to count the second one as part of top 10, right?

 

[ilmaggy]

Yes that's exactly what I mean. So only 1 picture per user.

 

[azzazzello]

I'll take a look at it more later, but how about using your original query, and making that a subset against which to run another select, with an ORDER BY user.id there. Since the set is already sorted, this second query should give you just the first foto of each user in the set, which is the most popular one for that user.

 

[azzazzello]

OK, following up on what I said, try this

SELECT id,naam,fid,foto,vtuid,foto_id,punten FROM (SELECT user.id AS id, naam, foto.fid AS fid, foto, votes.vote_to_user_id as vtuid, foto_id, sum( rating ) AS punten FROM votes, foto, user WHERE votes.foto_id = foto.fid AND user.id = votes.vote_to_user_id GROUP BY votes.foto_id ORDER BY punten) as t1 GROUP BY id ORDER BY punten;



Basically the inside is your original query, which is used as a set that is being ordered by user id. Since it is sorted in descending order, the value of the one with largest sum(rating) is used and that is exactly what you want

 

[ericbrunson]

Of course you'll require MySQL >4.1 to do sub-selects.

 

[azzazzello]

yes yes! of course! if you are not at 4.1, you will need to use temporary tables

 

[ilmaggy]

Thank you very much. It works fine now!
I couldn't possibly solve it myself. Subqueries are new to me. So I've learned from it too.