troubleshooting Question

How to write a sub query with multiple conditions

Avatar of nhmedia
nhmedia asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
5 Comments1 Solution355 ViewsLast Modified:
I have an SQL view containing many hundreds of pieces of content
And I have a table that has a record for every time those items have been viewed on a website.
I now need to extract the 20 most popular items
I have written a sub query to get the 20 most commonly viewed items:
SELECT top 20 ContentID , COUNT(ContentID) AS ID FROM  table1 (nolock) where SiteID = 26 and ContentID not in (0,-1)
 and UserAgent not like '%bot%' and  date='2011-05-16' GROUP BY  ContentID order by ID desc

But when I try something like this:
Select  * from View1 where ContentID in (SELECT top 20 ContentID , COUNT(ContentID) AS ID FROM  table1 (nolock) where SiteID = 26 and ContentID not in (0,-1)
 and UserAgent not like '%bot%' and  date='2011-05-16' GROUP BY  ContentID order by ID desc)

I get an error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Could anyone suggest how I can structure this to query to get what I need?
ASKER CERTIFIED SOLUTION
Ephraim Wangoya
Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros