Hi again

I have another table

Create table sh

(cartid int , shcode int,regdate date)

Insert Into sh values(44,1,'2015-01-01'),(44,2,'2015-01-02'),(44,3,'2015-01-03'),(55,4,'2015-01-01')

Select * from P Inner Join Po On P.id=PO.PID

Inner Join sh On sh.cartid=PO.Cartid

But I need Shcode with max Date . For example I Need this result

-- I Nead This Result :

Row 1 : Userid=300, Cartid=44, pay=3500, Back = 500, Pure = 3000 , ShCode = 3

Row 2 : Userid=301, Cartid=55, pay=950, Back = 0 , Pure = 950 , SHCode=4

Row 3 : Userid=302, Cartid=66, pay=3000, Back = 3000, Pure = 0 ,SHCode=Null

I Use CTE For sh table. But it get me 90 percent Cost :

,Sh

As

(

SELECT SHCode,Cartid,Row_number() Over(Partition By Cartid order by regdate desc) Rownumsh

FROM sh

)

... And in query

...Inner Join sh on sh.cartid=po.cartid and rownumsh=1

1- How to join this with old query

2- Is there any solution with better performance?