Click here to see the full article post
It seems that the second answer can not pass the test case, the reason is that it didn't set the @rank and @cp in the first brackets, so the @rank will always be 1. The right solution is to set these two variables in the first brackets.
@moveingsun Thanks for your comments and code fix. I've used your solution in this article.
AS ( SELECT COMPANY, COUNT (1) CNT
GROUP BY COMPANY)
FROM EMPLOYEE E, TMP T
WHERE E.COMPANY = T.COMPANY
AND (SELECT COUNT (DISTINCT SALARY)
FROM EMPLOYEE F
WHERE F.SALARY < E.SALARY AND F.COMPANY = E.COMPANY) IN (TRUNC(T.CNT/2), T.CNT/2 - 1)
ORDER BY E.COMPANY, E.SALARY;
regarding approach 2's session variables (@rank, @prev), doesn't the select clause occur before the order by clause? How is it that the ranking changes when you call order by? I thought the rank is evaluated first, then order by occurs.
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.