Search code examples
mysqlsqlerror-code

RANK() used in mysql and when I use a subquery to get rows of a particular rank the "Error Code: 1242. Subquery returns more than 1 row" is thrown

I want to arrange a table using rank function, partition it by one attribute and order it by another and then display rows of Rank 1.

The query:

SELECT *,RANK() OVER (PARTITION BY sales.id ORDER BY sales.time DESC ) as sales_row_num 
FROM sales 
WHERE ( SELECT RANK() OVER (PARTITION BY sales.id ORDER BY sales.time DESC )
        FROM sales ) = 1 ;

Error Code: 1242. Subquery returns more than 1 row

What do I do? How else can I frame my subquery as it is the reason for the error?

Solution 
  • WITH cte AS (
    SELECT *,
           RANK() OVER (PARTITION BY sales.id ORDER BY sales.time DESC ) as sales_row_num 
    FROM sales 
)
SELECT *
FROM cte
WHERE sales_row_num = 1 ;