Fetch a row which has the maximum value for a column

Question

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

How do you think it might work? You may find you get a better quality of answer and that people are more willing to help you if you are able to demonstrate that you have tried something for yourself. — Ben
– Ben, Commented Jun 10, 2012 at 11:57
Since your tables can have duplicate TNo(s), there can be 2 Max value — codingbiz
– codingbiz, Commented Jun 10, 2012 at 13:10

Tim Schmelter · Accepted Answer · 2012-06-10 12:03:24Z

1

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

answered Jun 10, 2012 at 12:03

Tim Schmelter

462k79 gold badges718 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bridge Over a year ago

I'm pretty sure you need the desc column in square brackets ;-)

user362283 Over a year ago

hi, I tried ti do an oder by cno & Tno but its not supported in CTE ?

user362283 Over a year ago

hi, i tried ur query but still getting two records for rowid; tno; cno rno 1 2488 22425 20882 1 2488 22411 22005

Tim Schmelter Over a year ago

@user362283: Edit your question and show your sample data and your desired result. Btw, you can always use SELECT TOP 1

user362283 Over a year ago

hi i haved added the example in question. Thanks

|

Collectives™ on Stack Overflow

Fetch a row which has the maximum value for a column

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related