TOP (n) PERCENT Problem

Question

TOP (n) PERCENT Problem

rbuschert

SSC Enthusiast

Points: 121
More actions
March 9, 2007 at 11:07 am

#172449

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Stephanie Giovannini SSCertifiable Points: 7422 More actions · Answer 1

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.

peterhe SSChampion Points: 11469 More actions · Answer 2

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.

rbuschert SSC Enthusiast Points: 121 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 4

What do you get for this?

SELECT TOP 75 PERCENT intValue

FROM #tmp

ORDER BY intValue ASC

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rbuschert SSC Enthusiast Points: 121 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)