Select top percent, in SQL Server, is off by 1?

Question

I want to use select statement to get 10% from 3661 records table. But I get 367 records in return, but I want only 366 records; but when I put 50%, I want to get 1831 records, not 1830 records. here is the statement I use:

SELECT top 10 percent *
FROM table_a

Answer 1

Unfortunately, that's just how the TOP x PERCENT clause works (relevant part highlighted):

PERCENT

Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.

If you want a different rounding scheme, you will have to do the calculation yourself:

SELECT TOP (SELECT COUNT(*) / 10 FROM table_a) * FROM table_a

This would use an integer division, which truncates the fractional part, i.e., it rounds down.

Since you want 50% of 3661 to yield 1831, I guess you want "round half up", which you would get using the ROUND function:

SELECT TOP (SELECT CONVERT(int, ROUND(COUNT(*) / 10.0, 0)) FROM table_a) * FROM table_a

Note that 10.0 instead of 10 is required to force a floating-point division instead of an integer division.