By ElGrig


2019-03-13 08:45:52 8 Comments

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

1 comments

@Randi Vertongen 2019-03-13 09:18:55

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

@ElGrig 2019-03-14 06:34:15

Thank you @RandiVertongen, but why it shows different result when I apply where filter? See, dbfiddle.uk/…

@Randi Vertongen 2019-03-14 08:12:11

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

@ElGrig 2019-03-14 08:56:51

ok thanks @RandiVertongen

Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] Efficient way to paginate in sql server with 50M plus data (sql server 17)

  • 2018-12-02 09:37:36
  • Aayush Dahal
  • 69 View
  • 0 Score
  • 2 Answer
  • Tags:   sql-server

1 Answered Questions

1 Answered Questions

2 Answered Questions

[SOLVED] How to select TOP 1 value from each column where value is not null

2 Answered Questions

[SOLVED] Group By With Rollup results table has totals at the top

2 Answered Questions

[SOLVED] Using TOP in INSERT INTO statement

3 Answered Questions

[SOLVED] How to select distinct TOP but group by column

1 Answered Questions

How to get paged resultset from a table?

1 Answered Questions

[SOLVED] TSQL Why is Top faster with a variable?

  • 2014-11-19 11:35:22
  • JamesStuddart
  • 1891 View
  • 10 Score
  • 1 Answer
  • Tags:   sql-server t-sql

Sponsored Content