Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 MAX(rownumber) and count issue Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, February 10, 2014 2:53 AM
 SSC Veteran Group: General Forum Members Last Login: Today @ 3:48 AM Points: 230, Visits: 490
 declare @a table( keyid int,keyname varchar(50) ) declare @b table( keyid int,keyname varchar(50) ) 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 values (1,'100') insert into @b values (3,'300') insert into @b values (5,'500') insert into @b values (6,'600') insert into @b values (8,'800') insert into @b values (8,'801')-- Below query gives totalrows = 7 , if I remark count ;with rowcte as (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 rowascfrom @a a left outer join @b b on a.keyid = b.keyid )select max(rowasc) totoalrows --,count(*) as totcountfrom rowcte-- Below query gives totalrows = 8 and totcount = 8 WHY ?;with rowcte as (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 rowascfrom @a a left outer join @b b on a.keyid = b.keyid )select max(rowasc) totoalrows ,count(*) as totcountfrom rowcte
Post #1539671
 Posted Monday, February 10, 2014 3:13 AM
 Say Hey Kid Group: General Forum Members Last Login: Wednesday, April 09, 2014 2:57 AM Points: 704, Visits: 3,270
 The first query gives me 8 and the second gives me 8, 8?! ---------------------------------------------------------It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lensSociety has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn
Post #1539673
 Posted Monday, February 10, 2014 3:22 AM
 SSC Veteran Group: General Forum Members Last Login: Today @ 3:48 AM Points: 230, Visits: 490
 I forgot to mention . This is in SQL 2005 SP2
Post #1539674
 Posted Monday, February 10, 2014 4:26 AM
 SSC-Addicted Group: General Forum Members Last Login: Today @ 3:58 AM Points: 464, Visits: 456
 I got 7 and 8,8 on .. 2008(RTM) developers and enterprise edition ... I'm stumped too... can somebody please explain the reason behind this output ?
Post #1539689
 Posted Monday, February 10, 2014 7:08 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 4:27 PM Points: 2,763, Visits: 5,899
 You should get 8 on both queries. A MAX() on a ROW_NUMBER() without PARTITION BY should give you the same values as COUNT(*).The reason is that you have keyids 1 and 8 repeated. You could check it with a simple query.`SELECT *from @a a left outer join @b b on a.keyid = b.keyid ` Luis C.I am a great believer in luck, and I find the harder I work the more I have of it. Stephen LeacockForum Etiquette: How to post data/code on a forum to get the best help
Post #1539745
 Posted Monday, February 10, 2014 11:17 PM
 SSC-Addicted Group: General Forum Members Last Login: Today @ 3:58 AM Points: 464, Visits: 456
 Exactly... that is what I tried before posting reply... it clearly shows that it should give 8 as an output but I did not get it... am I missing something here ?
Post #1540042
 Posted Tuesday, February 11, 2014 1:26 AM
 SSC-Addicted Group: General Forum Members Last Login: Today @ 4:15 AM Points: 468, Visits: 811
 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 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 values (1,'100')insert into @b values (3,'300')insert into @b values (5,'500')insert into @b values (6,'600')insert into @b values (8,'800')insert into @b values (8,'801')INSERT into @cselect 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 rowascfrom @a aleft join @b b on a.keyid = b.keyidSELECT MAX(rowasc), COUNT(akeyid)from @cSELECT MAX(rowasc)from @c`Its a bug in 2005, 2008, 2008R2 .... but its running perfectly on the 2012. here is the link http://support.microsoft.com/kb/2565683kindly upgrade your sql server to latest SPs if possible.
Post #1540069
 Posted Tuesday, February 11, 2014 5:56 AM
 SSC-Addicted Group: General Forum Members Last Login: Today @ 3:58 AM Points: 464, Visits: 456
 Thank you for that Service pack was applied and got my Inner peace...
Post #1540158

 Permissions