Filter SQL Server data according to its max value

Question

I have one SQL Server 2008 table like:

    +------+-------+--------------------------------------+
    | id   | level | content                              |
    +------+-------+--------------------------------------+
    | 1    | 1     | ...                                  |
    | 2    | 2     | ...                                  |
    | 1    | 2     | ...                                  |
    | 1    | 3     | ...                                  |
    | 2    | 1     | ...                                  |
    | 1    | 4     | ...                                  |
    | 3    | 1     | ...                                  |
    +------+-------+--------------------------------------+

For every id, it may have three, two or four levels saved in table like above. How can I get the data for every id:

1. every id has at most three records in final table
2. if the max level of one id is higher than 3, the three records' level is from max to max-3;
3. if the max level of one id is equal or less than 3, just keep them as they are.

so the final table which I would like to get is:

    +------+-------+--------------------------------------+
    | id   | level | content                              |
    +------+-------+--------------------------------------+
    | 1    | 1     | ...                                  |
    | 2    | 2     | ...                                  |
    | 1    | 2     | ...                                  |
    | 1    | 3     | ...                                  |
    | 2    | 1     | ...                                  |
    | 3    | 1     | ...                                  |
    +------+-------+--------------------------------------+

How can I the lines? Thanks a lot.

Answer 1

I think you want the 3 latest levels per id. If so, you can use window functions like so:

select *
from (
    select t.*, row_number() over(partition by id order by level desc) rn
    from mytable t
) t
where rn <= 3