MAX() is working upon as per the definition of the base table.

in your query u are using left join, if you try to use right join u will be even more surprised.

Once you are done with the surprised run the below query

declare @a table( keyid int,keyname varchar(50) )

declare @b-2 table( keyid int,keyname varchar(50) )

declare @C table( akeyid int,akeyname varchar(50), bkeyid int, bkeyname varchar(50) ,rowasc int )

insert into @a values (1,'10')

insert into @a values (1,'10')

insert into @a values (2,'20')

insert into @a values (4,'40')

insert into @a values (6,'60')

insert into @a values (8,'80')

insert into @a values (9,'90')

insert into @b-2 values (1,'100')

insert into @b-2 values (3,'300')

insert into @b-2 values (5,'500')

insert into @b-2 values (6,'600')

insert into @b-2 values (8,'800')

insert into @b-2 values (8,'801')

INSERT into @C

select a.keyid as akeyid,a.keyname as akeyname , b.keyid as bkeyid ,b.keyname as bkeyname,

row_number() over( order by a.keyid asc ) as rowasc

from @a a

left join @b-2 b on a.keyid = b.keyid

SELECT MAX(rowasc), COUNT(akeyid)

from @C

SELECT MAX(rowasc)

from @C

Its a bug in 2005, 2008, 2008R2 .... but its running perfectly on the 2012. :w00t:

here is the link http://support.microsoft.com/kb/2565683

kindly upgrade your sql server to latest SPs if possible.