Group by 1

[cyberrate]

Hello!

I have a table t_photos from which i want to make a top 20 of all photos so i try:

select * from t_photos where user_sex = '0' and status='1' and rated_times > '15' and rated_times < '900' group by user_id order by score desc

i put 'group by user_id' because i want one user to have only one photo in the top 20 list of course photo with highest score, but this query does not return record of the photo with highest score. For example if user has 5 photos ( there is 5 records in table t_photos with user_id = '11') and one of them has score 5.2 another has 4.86 and rest of the photos has some score, the query above returns the record that has 4.86 in score field.

Now if i remove group by from the query it does sort records by score so photo with score 5.2 appears above all, but there will be 2 records of same user in the top 20.

Is there any solution for this?

 

[TonyGroves]

How about something like:
[tt]
SELECT user_id,MAX(score) topscore
FROM
t_photos
WHERE
user_sex = '0'
AND status='1'
AND rated_times > '15'
AND rated_times < '900'
GROUP BY user_id
ORDER BY topscore DESC
LIMIT 20
[/tt]
It doesn't identify the particular photos, but it does give the top score for each user.

-----
ALTER world DROP injustice, ADD peace;

 

[cyberrate]

Thanx for your reply!

well the query you suggested works but... in a strange way, you see i also need an id of the photo so i tryed:

SELECT id, user_id, MAX( score ) topscore
FROM t_photos
WHERE user_sex = '0' AND
STATUS = '1' AND rated_times > '15' AND rated_times < '900'
GROUP BY user_id
ORDER BY topscore DESC
LIMIT 20

and this returns what i need and how i need but with the wrong id:

id user_id topscore
120 99 5.13


while i'm sure that id of the photo with score 5.13 is 121 and user_id is 99 thow photo with id 120 (the id that query returns) belongs to the same user_id - 99, any ideas?

 

[TonyGroves]

What's happening there is that for each user, it's examining all his/her records, and getting from them the highest score and one (which one is undefined) of the 'id' values.

You have two other options: either use two queries and a temporary table, or take a chance with MySQL 4.1 (alpha) and use a sub-query.


The first approach would involve:
[tt]
CREATE TEMPORARY TABLE t AS
SELECT user_id,MAX(score) score
FROM
t_photos
WHERE
user_sex = '0'
AND status='1'
AND rated_times > '15'
AND rated_times < '900'
GROUP BY user_id
ORDER BY topscore DESC
LIMIT 20;

SELECT p.id,p.user_id,p.score
FROM t JOIN t_photos p USING (user_id,score)
GROUP BY p.user_id,p.score
ORDER BY p.score DESC;

DROP TABLE t;
[/tt]
The GROUP BY in the second query ensures that where a user has more than one photo with the same top score, only one of them is returned.


The second approach would involve something like:
[tt]
SELECT p.id,p.user_id,p.score
FROM
t_photos p
JOIN
(
SELECT user_id,MAX(score) score
FROM
t_photos
WHERE
user_sex = '0'
AND status='1'
AND rated_times > '15'
AND rated_times < '900'
GROUP BY user_id
ORDER BY topscore DESC
LIMIT 20
)
t USING (user_id,score)
GROUP BY p.user_id,p.score
ORDER BY p.score DESC
[/tt]

-----
ALTER world DROP injustice, ADD peace;

 

[cyberrate]

Thank you for your input, TonyGroves !

I'll just make the query as you suggested the first time and then

select * from t_photos where score = '$topscore' and user_id = '$user_id'

$topscore and $user_id are the values that i get from the query that you suggested, this way it will be easyer.

Thank you!