select top 10 percent *
from sys.databases
order by database_id
I want to get the same 10 percent result using offset but this query works wrong
offset 0 rows
fetch next (select cast((count(*) * 10/100) as int ) from sys.databases) rows only
offset-fetchsql servert-sqltop
select top 10 percent *
from sys.databases
order by database_id
I want to get the same 10 percent result using offset but this query works wrong
offset 0 rows
fetch next (select cast((count(*) * 10/100) as int ) from sys.databases) rows only
Best Answer
TOP PERCENT
Source
Meaning that
TOP percent
is rounding up, we could use theCEILING()
function on anumeric
value to get the same result.You could change the query like this:
To get the same amount.
Issues with applying a filter.
If you add a where clause the results could differ, the reason being that in the changed query, you would need to add the where clause also on the query in the
OFFSET FETCH
part.These two queries could return different results:
DB<>Fiddle
These two queries should return the same results:
DB<>Fiddle