CODEWITHSUNDEEP
sql-server-2005
user362283
user362283

Reputation: 95

Fetch a row which has the maximum value for a column

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

Answers (1)

Tim Schmelter
Tim Schmelter

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

Related Questions