Subquery in select not working in SQL Server

by

The subqueries you are using in your select statement should return single value for your query to run without error. The following query will run without any issue, but it won’t give you the result you are expecting.

SELECT TOP 10
    (SELECT g_name FROM vgsales$ x WHERE g_platform = 'X360' AND a.g_rank = x.g_rank) AS g_namex360, 
    (SELECT g_name FROM vgsales$ p WHERE g_platform = 'PS2' AND a.g_rank = p.g_rank) AS g_nameps2
FROM 
    vgsales$ a
ORDER BY 
    Global_Sales;

The following query, although it may not be in the format you want, will return the correct result.

WITH cte AS
(
    SELECT
       *,
       ROW_NUMBER() OVER(PARTITION BY g_platform ORDER BY Global_Sales DESC) AS RN
    FROM vgsales$ 
)
SELECT *
FROM cte
WHERE RN <= 10

More Related Contents:

  1. How to multiply column in Sql
  2. how to select next n letters after one specific letter using function in sql server by giving function name,specific letter, no of letters next
  3. SQL server query to output the data shown below
  4. What makes a SQL statement sargable?
  5. Optimistic vs. Pessimistic locking
  6. In SQL Server, how do I generate a CREATE TABLE statement for a given table?
  7. SQL Server query to find all permissions/access for all users in a database
  8. Select columns from result set of stored procedure
  9. Pass Dictionary to Stored Procedure T-SQL
  10. Why is 1899-12-30 the zero date in Access / SQL Server instead of 12/31?
  11. Connecting to SQL Server LocalDB using JDBC
  12. RODBC odbcDriverConnect() Connection Error
  13. How can I do something like: USE @databaseName
  14. SSIS Excel Import Forcing Incorrect Column Type
  15. error when inserting into table having instead of trigger from entity data framework
  16. How to query values from xml nodes?
  17. Is there a way to not use square brackets in SQL Server?
  18. How to query for Xml values and attributes from table in SQL Server?
  19. Avg of float inconsistency
  20. Getting a warning when using a pyodbc Connection object with pandas
  21. How can I get the list of tables in the stored procedure?
  22. How to find the boundaries of groups of contiguous sequential numbers?
  23. “Prevent saving changes that require the table to be re-created” negative effects
  24. What are the differences between Merge Join and Lookup transformations in SSIS?
  25. Table Valued Function Killing My Query Performance
  26. freeTDS not using its config
  27. @@ERROR and/or TRY – CATCH
  28. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)
  29. How to make a passthrough / passthru query editable?
  30. How to do Sql Server CE table update from another table

Leave a Comment