Dynamic TOP N / TOP 100 PERCENT in a single query based on condition

Dynamic TOP N / TOP 100 PERCENT in a single query based on condition

A local variable @V_COUNT INT. If the variable @V_COUNT is '0'(zero) the return all the records from table otherwise return the number of {@V_COUNT} records from table. For example if @V_COUNT = 50, return TOP 50 records. If @V_COUNT is 0 then return TOP 100 PERCENT records. Can we achieve this in a single query?

Sample query :

DECLARE @V_COUNT INT = 0

SELECT  TOP (CASE WHEN @V_COUNT > 0 THEN  @V_COUNT ELSE  100 PERCENT END) *
FROM    MY_TABLE
ORDER BY COL1

Incorrect syntax near the keyword 'percent'


A better solution would be to not use TOP at all - but ROWCOUNT instead:

SET ROWCOUNT stops processing after the specified number of rows. ... To return all rows, set ROWCOUNT to 0.

Please note that ROWCOUNT is recommended to use only with select statements -

Important Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax.

DECLARE @V_COUNT INT = 0

SET ROWCOUNT @V_COUNT -- 0 means return all rows...

SELECT   *
FROM    MY_TABLE
ORDER BY COL1

SET ROWCOUNT 0 -- Avoid side effects...

This will eliminate the need to know how many rows there are in the table

Be sure to re-set the ROWCOUNT back to 0 after the query, to avoid side effects (Good point by Shnugo in the comments).

Or do I have to dynamically create the query without the TOP (@x) if they Well, it looks like TOP is a BIGINT if you aren't using a PERCENT. to the TOP rather than to provide a presentation order for results and SELECT TOP 100 PERCENT can be used to bypass any errors with using "TOP" in a query. As I have shown, by using the What-If Parameter and some DAX there is now a way to easily create a dynamic TopN that is easy for your users to use within their reports. As always, if there are any questions or comments, please leave them in the section below. Everyone's tags (1): ‎03-01-2018 07:56 PM. Email to a Friend.


Instead of 100 percent you can write some very big number, which will surely be bigger than possible number of rows returned by the query, eg. max int which is 2147483647.

most records from a table based on a condition. This result set can be used for further analysis. For example, using Top-N analysis we can perform the following​  How to Dynamically Display the Top N vs Other in Tableau. The first step to showing the dimension members of the Top N and grouping everything else into one line is to create a parameter for the Top N. This will eventually allow the end user to choose how many individual dimension members to show (i.e. Top 5, Top 6, Top 7, and so on).


You can do something like:

DECLARE @V_COUNT INT = 0

SELECT  TOP (CASE WHEN @V_COUNT > 0 THEN  @V_COUNT ELSE (SELECT COUNT(1) FROM MY_TABLE) END) *
FROM    MY_TABLE

Otherwise, TOP returns the first N number of rows in an undefined order. Indicates that the query returns only the first expression percent of rows from the result set. Or, one may be deleted, five updated, and four inserted, and so on. Notice that the top five employees are now inserted based on the  As I have shown, by using the What-If Parameter and some DAX there is now a way to easily create a dynamic TopN that is easy for your users to use within their reports. As always, if there are any questions or comments please leave them in the section below. PassedBI February 12, 2018 at 7:28 am. Hi, Gilbert, nice post! little remark about.


DECLARE @V_COUNT int = 3
select * 
from 
  MY_TABLE 
ORDER BY 
Service_Id asc  
offset case when @V_COUNT >0 then  ((select count(*) from MY_TABLE)- @V_COUNT) else @V_COUNT end rows

100 0 50 -150 -100 -50 BPUS II SA 2PO 20 30 40 50 60 70 80 90 100 110 0 20 Percent improvement (relative to that of a newly selected view set) as h is increased. and must be able to tune their performance to changing conditions on-the-fly. One important area of future work is to consider how best the dynamic view  The SELECT TOP statement returns a specified number of records. SELECT TOP is useful when working with very large datasets. Non SQL Server databases use keywords like LIMIT, OFFSET, and ROWNUM. Results: 10 records.


SET ROWCOUNT forces you into procedural logic. Furthermore, you'll have to provide an absolute number. PERCENT would need some kind of computation...

You might try this:

DECLARE @Percent FLOAT = 50;

SELECT  TOP (SELECT CAST(CAST((SELECT COUNT(*) FROM sys.objects) AS FLOAT)/100.0 * CASE WHEN @Percent=0 THEN 100 ELSE @Percent END +1 AS INT))  o.*
FROM    sys.objects o
ORDER BY o.[name];

This looks a bit clumsy, but the computation will be done once within microseconds...

Specifies the number or percent of rows that are updated. expression The rows referenced in the TOP expression used with INSERT, and reference exactly one base table in the FROM clause of the view. 100),(2, 200); WITH cte AS (​SELECT * FROM @x) UPDATE x -- cte is referenced by the alias. Don't use TOP 100 PERCENT, use TOP n, where N is a number. The TOP 100 PERCENT (for reasons I don't know) is ignored by SQL Server VIEW (post 2012 versions), but I think MS kept it for syntax reasons. TOP n is better and will work inside a view and sort it the way you want when a view is used initially, but be careful.


Power BI community – Dynamic Top N and Others category as the one that will I show in this blog post and follows these steps: 1) create a new table in the data model (either with Power Query or [RankItem])), /* need to obey current filters on base table */ I removed this condition on the final filter. Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM. SQL Server / MS Access Syntax: SELECT TOP number|percent column_name (s) FROM table_name. WHERE condition; SELECT column_name (s) FROM table_name. WHERE condition. SELECT column_name (s)


Dynamically display the Top N based on the number of dimension members you the Top N and grouping everything else into one line is to create a parameter for A new dialog will appear where you can set the conditions for which states  Combines the results of more than one SELECT statement into a single query. ALL or DISTINCT control which rows are included in the final result set. ALL causes all rows to be included, even if the rows are identical.


This content is excerpted from my book, Innovative Tableau: 100 More Tips, even trickier, you can have measure filters and dimension filters, but the condition tab in a Sometimes you simply want to show the top N (i.e. Top 5, Top 10, etc.)​  Click on New Measure on the Home ribbon and enter the following DAX formula. Total Sales ALLSELECTED = CALCULATE ( sum ( ‘Clothing Sales' [Sales] ) , ALLSELECTED ( ‘Clothing Sales’ ) ) Add this new measure into our existing table. AllSelected Filter Context.