Return only rows with maximum key

Question

I have two columns in my table:

- RECE_KEY
- INVE_KEY

I want to return only rows for INVE_KEY where RECE_KEY is max.

Example:

INVE_KEY = 1 is included in RECE_KEY = 1,2,3. Max RECE_KEY in this example is 3, so correct result would be:

+----------+----------+
| RECE_KEY | INVE_KEY |
+----------+----------+
|        3 |        1 |
|        3 |        1 |
|        3 |        1 |
|        3 |        1 |
+----------+----------+

For the whole table expected result would be:

+----------+----------+
| RECE_KEY | INVE_KEY |
+----------+----------+
|        3 |        1 |
|        3 |        1 |
|        3 |        1 |
|        3 |        1 |
|        3 |        4 |
|        5 |        5 |
|        5 |        5 |
|        5 |        5 |
+----------+----------+

I've tried to solve this grouping and where conditions but without success. I guess I am missing something?

Example table is below:

DECLARE @JEREIN TABLE
(
RECE_KEY INT,
INVE_KEY INT
)

INSERT INTO @JEREIN 
VALUES(1,1),(1,1),(1,1),(2,1),(2,1),(3,1),(3,1),(3,1),(3,1),(2,4),(2,4),(3,4),(3,5),(3,5),(5,5),(5,5),(5,5)

Answer 1

The first choice would be ranking function, but you can also use corretalted suqbuery :

select t.*
from @JEREIN t
where RECE_KEY = (select max(t1.RECE_KEY) from @JEREIN t1 where t1.INVE_KEY = t.INVE_KEY);

However, you can also use dense_rank with ties clause :

select top (1) with ties t.*
from @JEREIN t
order by dense_rank() over (partition by INVE_KEY order by RECE_KEY desc);