[Solved] 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

1

solved Subquery in select not working in SQL Server