Why Does My SQL Subquery Return 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS?'

SQL Subquery Error: Single Expression in SELECT List

This SQL query generates an error:

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 message, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS," indicates a problem with the subquery's SELECT clause. The subquery attempts to return two columns: A_ID and COUNT(DISTINCT dNum). When using IN (or other comparison operators like =, !=, etc.), the subquery's SELECT list must contain only a single column.

Solution:

The corrected query should return only A_ID from the subquery:

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;

Notice the changes:

• The subquery's SELECT clause now only includes A_ID.
• The ORDER BY clause, which was previously within the subquery, has been moved to the outer query. This is because the original ORDER BY was attempting to sort the subquery results before they were used in the IN clause; the sorting should apply to the final result set.

This revised query correctly selects the count of distinct dNum values where A_ID is found within the results of the subquery. The subquery efficiently identifies the top 10% of A_ID values based on the count of distinct dNum, and the outer query then counts the distinct dNum values associated with those selected A_IDs. The final ORDER BY clause sorts the results based on the count of distinct dNum values.

The above is the detailed content of Why Does My SQL Subquery Return 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS?'. For more information, please follow other related articles on the PHP Chinese website!