My table is like
P_ID | NAME | SRNO | Rate
1 | PR1 | 1 | 50
1 | PR1 | 2 | 60
1 | PR1 | 3 | 80 ----
2 | PR2 | 1 | 10
2 | PR2 | 2 | 20 ----
3 | PR3 | 1 | 70 ----
4 | PR4 | 1 | 25
4 | PR4 | 2 | 35 ----
I want to details whose SrNo is Maximum for each product.
Like this :
P_ID | NAME | SRNO | Rate
1 | PR1 | 3 | 80
2 | PR2 | 2 | 20
3 | PR3 | 1 | 70
4 | PR4 | 2 | 35
How shall I do it???
You can use a correlated subquery:
select t.*
from mytable t
where t.srno = (select max(srno) from mytable t1 where t1.p_id = t.p_id)
With an index on (p_id, srno)
, this should be an efficient solution.
Anoter common solution is to use row_number()
:
select pid, name, srno, rate
from (
select t.*, row_number() over(partition by p_id order by srno desc) rn
from mytable t
) t
where rn = 1