problem with query

Question

I have two tables in my database first saveimage which contains id and url of each image second score which contains id and score of each image this scores are not unique - each row in score table is user rating to a image so one image may has several rows in score.

Now I want find images with highest score in database

I tried

select * from saveimage 
where saveimage.id in (select top 100 id,SUM(avgscore) 
                       from score 
                       group by id  
                       order by SUM(avgscore) desc)

Which is not working because

Only one expression can be specified in the select list

So what can i do?

Jamiec · Accepted Answer · 2011-09-21 11:55:16Z

2

This should work:

SELECT TOP 100 img.id,
       img.url,
       SUM(sc.avgscore) as totalScore
FROM saveimage img
INNER JOIN score sc
ON img.id = sc.id
GROUP BY img.id, img.url
ORDER BY SUM(sc.avgscore) DESC

answered Sep 21, 2011 at 11:55

Jamiec

136k15 gold badges143 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MattW · Accepted Answer · 2011-09-21 11:53:49Z

1

You can't have 2 columns in your subquery (you have id and sum(avgscore)).

If you are trying to find the top 100 scores for each image, try something like this:

select * 
from saveimage 
where saveimage.id in 
  (select top 100 id 
   from score 
   group by id  
   order by SUM(avgscore) desc)

answered Sep 21, 2011 at 11:53

MattW

13.2k5 gold badges42 silver badges65 bronze badges

Collectives™ on Stack Overflow

problem with query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related