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

by

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)

More Related Contents:

  1. Calculate a Running Total in SQL Server
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. Not equal != operator on NULL
  4. SET NOCOUNT ON usage
  5. How can I get column names from a table in SQL Server?
  6. How can I select the first day of a month in SQL?
  7. Subquery using Exists 1 or Exists *
  8. Truncate (not round) decimal places in SQL Server
  9. Most efficient T-SQL way to pad a varchar on the left to a certain length?
  10. Difference between CTE and SubQuery?
  11. Reference alias (calculated in SELECT) in WHERE clause
  12. TSQL PIVOT MULTIPLE COLUMNS
  13. When to use Common Table Expression (CTE)
  14. How to send email from SQL Server?
  15. How To Create Table with Identity Column
  16. SQL query to find Nth highest salary from a salary table
  17. How to split string using delimiter char using T-SQL?
  18. Calculate the number of records for each date between 2 dates
  19. Get the records of last month in SQL server
  20. How to compare datetime with only date in SQL Server
  21. I want to use CASE statement to update some records in sql server 2005
  22. SQL server select distinct rows using most recent value only
  23. Access to Result sets from within Stored procedures Transact-SQL SQL Server
  24. alternatives to REPLACE on a text or ntext datatype
  25. How to return a incremental group number per group in SQL
  26. Eliminate and reduce overlapping date ranges
  27. SQL – How do I get only the numbers after the decimal?
  28. How to retrieve records for last 30 minutes in MS SQL?
  29. Group consecutive rows of same value using time spans
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment