4
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
3

TOP 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.

Source

Meaning that TOP percent is rounding up, we could use the CEILING() function on a numeric value to get the same result.

You could change the query like this:

SELECT *
FROM sys.databases
ORDER BY database_id
OFFSET 0 ROWS
FETCH NEXT (SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases) ROWS ONLY;

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:

SELECT *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id
OFFSET 0 ROWS
FETCH NEXT (SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases) ROWS ONLY;


SELECT top 10 percent *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id;

DB<>Fiddle

These two queries should return the same results:

SELECT *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id
OFFSET 0 ROWS
FETCH NEXT (SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases where name not like '%A%') ROWS ONLY;


SELECT top 10 percent *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id;

DB<>Fiddle

3
  • Thank you @RandiVertongen, but why it shows different result when I apply where filter? See, dbfiddle.uk/…
    – igelr
    Mar 14 '19 at 6:34
  • Hey @ElGrig no problem, good question! It is because of the SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases not having the where clause. You would need to add the same where clause to the count aswell. I will add it to the answer Mar 14 '19 at 8:12
  • 1
    ok thanks @RandiVertongen
    – igelr
    Mar 14 '19 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.