How to filter data based on top values from a specific year in SQL?-CodePudding

Let's assume my data looks like this:

    year   person        cash
0   2020   personone     29
1   2021   personone     40
2   2020   persontwo     17
3   2021   persontwo     13
4   2020   personthree   62
5   2021   personthree   55

What I want to do is the following. I'd like to get the top 2 people comparing their cash based on year 2021. We can see that in 2021 personone and personthree are the top 2 people, then it can be ordered by cash in 2021. So the output I'm after is:

    year   person        cash
0   2020   personthree   62
1   2021   personthree   55
2   2020   personone     29
3   2021   personone     40

I've been trying a similar approach to the one described here with no much luck.

CodePudding user response：

We can use DENSE_RANK here:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY person ORDER BY cash DESC) dr
    FROM yourTable
    WHERE year = 2021
)

SELECT *
FROM yourTable
WHERE person IN (SELECT person FROM cte WHERE dr = 2);