March 9, 2007 at 11:07 am
I'm running into a problem in SQL 2005 with an incorrect number of rows being returned from a TOP (n) PERCENT query in some cases. The example below illustrates the problem. I think it should only return 21 rows (75% of 28 is 21) but it actually returns 22 rows. Note, in SQL 2000 21 rows are returned. Has anyone else had this problem?
------------------------------------------
CREATE TABLE #tmp
(intValue int)
DECLARE @value int
SET @value = 1
WHILE @value <= 28
BEGIN
INSERT #tmp
VALUES (@value)
SET @value = @value + 1
END
SELECT TOP (75) PERCENT intValue
FROM #tmp
ORDER BY intValue ASC
March 9, 2007 at 11:55 am
Interesting... I'm running SQL Server 2005 Dev Ed. Your script performs the same on my server. I've been testing it with other numbers divisible by 4, but errors don't occur on all of them. I've had errors using 28 rows and 56 rows.
Also, the top 50 percent of a table with 14 rows will yield 8 rows, instead of 7. But the top 33 percent of a table with 21 rows is, correctly, 7 rows.
The problem seems to occur in tables with a row count divisible by 7 in which the percent calculation should yield an exact number of rows (no rounding needed). In these cases, one extra row is returned.
March 12, 2007 at 7:17 am
SQL server converts the result of top expression to float. This results in a wrong number of records returned. You can confirm this by the execution plan.
Anyway, it sounds a bug to me. MS should fix this.
March 12, 2007 at 8:04 am
I reported this to MS as a bug. Here's something that should work to return the correct number of rows using NTILE:
--------------------------
CREATE TABLE #tmp
(intValue int)
DECLARE @value int
SET @value = 1
WHILE @value <= 28
BEGIN
INSERT #tmp
VALUES (@value)
SET @value = @value + 1
END
SELECT intValue
FROM (SELECT intValue, NTILE(4) OVER(ORDER BY intValue) AS Quartile FROM #tmp) AS T
WHERE Quartile < 4
March 12, 2007 at 11:45 pm
What do you get for this?
SELECT TOP 75 PERCENT intValue
FROM #tmp
ORDER BY intValue ASC
--Jeff Moden
March 13, 2007 at 5:59 am
I get the same thing without the "()". In SQL 2005 BOL, the "()" are noted as the new correct syntax. It still works without for backward compatibility.
March 13, 2007 at 7:05 am
Heh... gotta love Microsoft... we're at SP2a and they still didn't get it right... thanks for the feedback and thanks for reporting it to MS.
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply