Subquery in select not working in SQL Server-CodePudding

I'm using SQL Server but somehow it won't accept my code (see below error/result). My goal here was to indicate top 10 games in global sales per gaming platform in each column

SELECT TOP 10 
    (SELECT g_name FROM vgsales$ WHERE g_platform = 'X360') AS g_namex360, 
    (SELECT g_name FROM vgsales$ WHERE g_platform = 'PS2') AS g_nameps2
FROM 
    vgsales$
ORDER BY 
    Global_Sales;

Error Result:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Below is the table vgsales$

CREATE TABLE vgsales$ (
g_rank INT,
g_name VARCHAR(100),
g_platform VARCHAR(100),
g_year INT,
g_genre VARCHAR(100),
g_publisher VARCHAR(100),
Global_Sales INT
);

CodePudding user response：

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