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?