You can translate the question and the replies:

Filter on a Rank function

Hi team! I'm facing a common scenario using the Rank function and need some help. Below is the sentence I'm using, basicaly a Rank function with a Partition and Order by conditions: rank() OVER ( PARTITION BY xx_fa_deprn_summary.asset_id, xx_fa_deprn_summary.book_type_code ORDER BY xx_fa_periods.fiscal_year DESC, xx_fa_periods.period_num DESC ) My question is pretty much common, I want to filter that rank without having to use a selection on top of the view that has this column. I tried using that sentence in the where clause but didn't work. I'm not using group by btw. Any thoughts there? Please, let me know if you need anything else from my end. Best, Fabian
RANK Function #Analytic function
user
31-10-2017 19:08:03 -0400
code

3 Answers

Hi, Analytical functions are executed after the processing of clauses like GROUP BY, HAVING, WHERE. You could execute the Rank function in the Select clause as it happens to execute after the Where clause. I would either create a Selection view or execute a subquery as mentioned below in the VQL shell to filter the RANK value results. select <field_name>, rank_field from (select <field_name>, RANK()OVER( PARTITION BY <field_name> ORDER BY <field_name>) rank_column from <view_name>) where rank_field =<value> Hope this helps!
Denodo Team
01-11-2017 09:10:48 -0400
code
Hi! Thanks for your quick answer! That's what I meant with "**I want to filter that rank without having to use a selection on top of the view that has this column. **", we have it on that way, but we thought we can get better performance by doing the filter before getting the results back. However, thanks again! Is there any other workaround? Best, Fabian
user
01-11-2017 09:16:00 -0400
Hi, Due to the logical order of the SQL queries execution, the "RANK" function cannot be used directly in the "WHERE" clause. As the "RANK" function can be delegated to the source, I would create a new base view using "Create from query" option with a SQL query by doing the following steps: * Open the desired data source and click on the "Create base view" option at the top. * Select the 'Create from query’ option. * Enter the view name and the SQL query as follows * select <column_name>, rank_column from (select <column_name>,RANK() OVER (PARTITION BY <column_name> ORDER BY <column_name>) rank_column from <table_name>) where rank_column =<value>. * Click on 'Save' to create a base view. From this base view, I would filter the "RANK" function without creating a selection view on top of this view. For more information, you can have a look at section [Creating Base Views from SQL Queries](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/jdbc_sources#creating-base-views-from-sql-queries) of Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
10-11-2017 05:17:08 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here