February 21, 2008 at 3:02 pm
When I run this query:
create table #temp (i int)
declare @i int
set @i = 1
while @i <= 14
begin
insert #temp (i) values (@i)
set @i = @i + 1
end
select top 50 percent * from #temp
drop table #temp
I consistently get 8 rows back. This is not isolated to a 14 row table, but the behaviour only surfaces for certain numbers of rows. (ex: 14, 28, 56, 110, 112...) This problem seems to have been introduced in SQK2K SP4. When I run the query on a SQL2K SP3 installation, I get the expected 7 rows back. Has anyone seen this before? Is there a fix out there? I don't want to have to reinvent the wheel to select a percentage of rows from a table, but I need accurate results.
February 21, 2008 at 3:24 pm
This is very interesting; I am not sure if you got to this point by a pure luck or you were actually R&D'ing.
In either case, it looks like in some odd number divisions by 50 percent gets to round-up the decimal position to the higher number. For instance, I was playing around with 25 and 50 percent and I got 13. But, the 14 and 50 percent is NOT making sense at all. It should be solid 7.
I hate to admit I do not have answer for you here today, but, I am going to think about it while driving to NorCal tomorrow (365 miles).
🙂
John Esraelo
p.s. I will be watching this thread and see who has what answer(s).
Cheers,
John Esraelo
February 21, 2008 at 5:00 pm
I just tried running it in SQL 2005 and got the oddball results. Same pattern (14, 28, etc.).
It's not even multiples of 7 (my first guess), since 42 doesn't do it. It seems to be 7 multiplied directly by powers of 2. I tried it for the first 12 powers of 2, and it got the same result each time: half + 1.
It's almost certainly a floating point problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 21, 2008 at 5:20 pm
All I can say is, CRAP!!!!!
As pjancicka states, it works fine on 2K SP3a, and doesn't work correctly on anything after that!
Hey! Some of you MVP's that are watching this... have MS add this fix to SP3 for 2K5 and tell them we want a final service pack for 2k as well. It should have this fix in it. This fix is critical to doing such things as calculating Medians and the like.
And, tell them service packs shouldn't break stuff like this! I don't know about a lot of other folks, but I use Median calculations A LOT and now I've got to go back and fix just a pot wad of code!!!
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply