Reputation: 95
I have the following table :
TNO : can be duplicate
CNo : can be duplicate
I need to select the maximum Tno against the CNo from Table A where a.RNO=B.RNO
Table A
Tno Rno name desc
100 200 adam aadddddd
100 200 adam aadddddd
101 201 king aasdfdsf
Table B
Cno Rno
101 200
Can you guys advise the best method please.
I tried the following but not much success
Hi,
Please see the sample data as reqeusted: so the query should
Actual Data :
Rowum cno Tno Rno
1 24908 24047 22021
1 24909 22424 22022
1 20438 22426 22023
Required Data : it should display the maximun tno against Cno
Rowum cno Tno Rno
1 24908 24047 22021
1 20438 22426 22023
Upvotes: 0
Views: 102
Reputation: 460380
One way is using a CTE
and ROW_NUMBER
function:
WITH CTE AS(
SELECT RowNum =
ROW_NUMBER()OVER(PARTITION BY A.Rno ORDER BY Tno DESC)
, A.Tno, A.Rno, name, [desc]
FROM TableA A INNER JOIN TableB B ON A.RNO=B.RNO
)
SELECT * FROM CTE
WHERE RowNum = 1
Upvotes: 1