How to get the last line row in each group

Question

Please can you help me to query the last LINE row for each OPPRID?

I'm using MS SQL and this query is not working. Can't figure out way the Max(line) result is not returning the maximum value (for OpprId = 2 it should be 4)

SELECT OpprId, Line, Step_Id
FROM mytable
WHERE OpprId IN (
   SELECT MAX(Line)
    FROM mytable
    GROUP BY OpprId
);

Table:

+--------+------+-------+
| OpprId | Line | Step  |
+--------+------+-------+
| 2      | 0    | Phone |
+--------+------+-------+
| 2      | 1    | SMS   |
+--------+------+-------+
| 2      | 2    | Quote |
+--------+------+-------+
| 2      | 4    | Order |
+--------+------+-------+
| 3      | 0    | Phone |
+--------+------+-------+
| 3      | 1    | Email |
+--------+------+-------+
| 4      | 0    | Phone |
+--------+------+-------+
| 4      | 1    | Quote |
+--------+------+-------+
| 4      | 2    | Email |
+--------+------+-------+

Expected result:

+--------+------+---------+
| OpprId | Line | Step_Id |
+--------+------+---------+
| 2      | 4    | Order   |
+--------+------+---------+
| 3      | 1    | Email   |
+--------+------+---------+
| 4      | 3    | Email   |
+--------+------+---------+

Answer 1

You can do this by performing an inner join:

SELECT a.OpprId, a.Line, a.Step_Id
FROM mytable a
INNER JOIN (
   SELECT OpprID,MAX(Line) as max_Line
    FROM mytable
    GROUP BY OpprId
) b on a.OpprID=b.OpprID and a.Line=b.max_line

You can use the row_number function as well:

select a.OpprId, a.Line, a.Step_Id
(select *,row_number() Over(Partition by OpprID Order by Line desc) as rownum
from mytable)a
WHERE a.rownum=1

Answer 2

You can use rank() :

select t.*
from (select t.*, rank() over (partition by OpprId  order by Line desc) as seq
      from table t
     ) t
where seq = 1;

This will return ties Line.