Posted By: Anonymous
My query is as follows, and contains a subquery within it:
select count(distinct dNum) from myDB.dbo.AQ where A_ID in (SELECT DISTINCT TOP (0.1) PERCENT A_ID, COUNT(DISTINCT dNum) AS ud FROM myDB.dbo.AQ WHERE M > 1 and B = 0 GROUP BY A_ID ORDER BY ud DESC)
The error I am receiving is …
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.`
When I run the sub-query alone, it returns just fine, so I am assuming there is some issue with the main query?
You can’t return two (or multiple) columns in your subquery to do the comparison in the
WHERE A_ID IN (subquery) clause – which column is it supposed to compare
A_ID to? Your subquery must only return the one column needed for the comparison to the column on the other side of the
IN. So the query needs to be of the form:
SELECT * From ThisTable WHERE ThisColumn IN (SELECT ThatColumn FROM ThatTable)
You also want to add sorting so you can select just from the top rows, but you don’t need to return the COUNT as a column in order to do your sort; sorting in the
ORDER clause is independent of the columns returned by the query.
Try something like this:
select count(distinct dNum) from myDB.dbo.AQ where A_ID in (SELECT DISTINCT TOP (0.1) PERCENT A_ID FROM myDB.dbo.AQ WHERE M > 1 and B = 0 GROUP BY A_ID ORDER BY COUNT(DISTINCT dNum) DESC)