You can translate the question and the replies:

Where Condition on Analytical Function Output

Denodo 6.0 20201123 I created a new field from the RANK analytical function in one of my datasets. In order to do so, I had to use the workaround of sending the data from a view down to a temporary table on a SQL server. This was because part of the dataset in the view where I wanted to apply RANK() was from a REST API data source. The Data Movement work around performed well and the RANK() function helped label duplicate values. I now want to only select values with a rank of 1, so I created another select view and added a where clause to the rank field. When running the query, my result set always comes back blank. Its as if the conditional can't work on the output of the rank function.
RANK Analytical WHERE condition
user
25-02-2021 14:03:53 -0500
code

3 Answers

Hi, I was able to retrieve the filtered results using the RANK function in the query without any issues. In your case, you could check the **[Execution Trace](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/querying_views/execution_trace_of_a_statement#execution-trace-of-a-statement)** to see if the query is completely delegated to the underlying data source or try executing the RANK() function as a subquery in the VQL shell as mentioned below. **SELECT <field_name>, rank_field FROM (SELECT <field_name>, RANK() OVER( PARTITION BY <field_name> ORDER BY <field_name>) rank_field from <view_name>) where rank_field =<value>;** Alternatively, you could also rewrite your query using the **[ROWNUM](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/other_functions#rownum)** function. You can find a similar community **[Filter on a Rank function](https://community.denodo.com/answers/question/details?questionId=9060g0000004ErGAAU&title=Filter+on+a+Rank+function)** that matches your scenario. For detailed information you could refer to the **[Analytic Functions (Window Functions)](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/analytic_functions_window_functions#analytic-functions-window-functions)** and **[Other Functions](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/other_functions#other-functions)** section of the Virtual DataPort VQL guide. Hope this helps!
Denodo Team
26-02-2021 07:29:31 -0500
code
I did happen to see the subquery recommendation, but that still didn't work. I checked the execution trace and you can see the subquery implemented. Its running on the datasource SQL server: ``` SELECT s0.searchparameter, s0.oid_site, s0.equipment, s0.equipmentserialnumber, s0.activity, s0.duration, s0.durationunit, s0.endtimedatetime, s0.starttimedatetime, s0.cycleidentifier, s0.channel_name, s0.iteration_time, s0.p10, s0.p20, s0.p30, s0.p40, s0.p50, s0.p60, s0.p70, s0.p80, s0.p90, s0.topsize, s0.s_36, s0.s_24, s0.s_18, s0.s_15, s0.s_12, s0.s_10, s0.s_9, s0.s_8, s0.s_7, s0.s_6, s0.s_5, s0.s_4, s0.s_3, s0.s_2, s0.s_1, s0.s_050, s0.s_025, s0.aspect_ratio_sample_average, s0.aspect_ratio_std_deviation, s0.sd_ratio_10_5, s0.image_rank FROM (SELECT t0.searchparameter AS searchparameter, t0.oid_site AS oid_site, t0.equipment AS equipment, t0.equipmentserialnumber AS equipmentserialnumber, t0.activity AS activity, t0.duration AS duration, t0.durationunit AS durationunit, t0.endtimedatetime AS endtimedatetime, t0.starttimedatetime AS starttimedatetime, t0.cycleidentifier AS cycleidentifier, t0.channel_name AS channel_name, t0.iteration_time AS iteration_time, t0.p10 AS p10, t0.p20 AS p20, t0.p30 AS p30, t0.p40 AS p40, t0.p50 AS p50, t0.p60 AS p60, t0.p70 AS p70, t0.p80 AS p80, t0.p90 AS p90, t0.topsize AS topsize, t0.s_36 AS s_36, t0.s_24 AS s_24, t0.s_18 AS s_18, t0.s_15 AS s_15, t0.s_12 AS s_12, t0.s_10 AS s_10, t0.s_9 AS s_9, t0.s_8 AS s_8, t0.s_7 AS s_7, t0.s_6 AS s_6, t0.s_5 AS s_5, t0.s_4 AS s_4, t0.s_3 AS s_3, t0.s_2 AS s_2, t0.s_1 AS s_1, t0.s_050 AS s_050, t0.s_025 AS s_025, t0.aspect_ratio_sample_average AS aspect_ratio_sample_average, t0.aspect_ratio_std_deviation AS aspect_ratio_std_deviation, t0.sd_ratio_10_5 AS sd_ratio_10_5, dense_rank() OVER ( PARTITION BY t0.cycleidentifier ORDER BY t0.iteration_time ASC ) AS image_rank FROM T_175690704883147211741866734301961774174722148934560550270244 t0) s0 WHERE s0.searchparameter = ? AND s0.oid_site = ? AND s0.image_rank = ? ```
user
26-02-2021 12:30:13 -0500
Hi, I was able to get the results for the RANK function by passing values in condition without any issues. In case of unexpected results, you could check whether the specified input parameters returns output as 1 for the **image_rank** field by passing the input parameters only for **searchparameter** and **oid_site** fields like **SELECT image_rank from <view_name> WHERE searchparameter = ? AND oid_site = ?** You could also try executing the same query in the underlying datasource to ensure the correctness of the data retrieved. If the issue persists and if you are a user with valid support access, you could open a support case at **[Denodo Support Site](https://support.denodo.com/)** and so that our support team will help you. Hope this helps!
Denodo Team
05-03-2021 05:53:47 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here