[
https://issues.apache.org/jira/browse/HIVE-17454?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16170092#comment-16170092
]
Zoltan Haindrich commented on HIVE-17454:
-----------------------------------------
Hello, I was planning to look into this...but I'm not yet understand the goal
of the query; could you give a smaller example possibly?
By using "unbounded preceeding" you would like to get the maximum so-far - am I
correct?
hmm...you've written "rows unbounded preceding and unbounded following" but
your query only contains unbounded preceding...
Could you give a sample which can be executed?
{code}
create table t (a int,b int);
insert into t values (1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(3,7),(3,8),(3,9);
select a,b,sum(b) over (partition by a order by b rows unbounded preceding)
from t;
{code}
> Hive - MAX Window function does not accept more than 1 sort key and does not
> work as expected with rows window clause
> ---------------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-17454
> URL: https://issues.apache.org/jira/browse/HIVE-17454
> Project: Hive
> Issue Type: Bug
> Components: Hive
> Affects Versions: 1.2.0
> Reporter: Krish B
> Priority: Minor
>
> Hi,
> I see MAX window function is throwing error if I use more than one order by
> clause. But when I use window clause (rows), it works fine but the results
> are not as expected as shown below.
> From the data shown below, I was expecting max_individual_id should be 42562
> for all the individuals under same HICN_NBR but I am getting different
> results. I tried "rows unbounded preceding and unbounded following" but
> results are still not as expected. Please let me know if this is an issue?
> Query:
> select individual_id, MAX(individual_id) over (partition by HICN_NBR order by
> eff_dt desc, cncl_dt desc, individual_id desc rows unbounded preceding) as
> max_individual_id,
> hicn_nbr, eff_dt, cncl_dt
> from MFW_MeasureMembership_BOTH MM
> where Remove_ASH <> 1 and Remove_AGB <> 1 and Remove_FARM_151 <> 1 and
> Remove_JCA <> 1 and Remove_CVTY <> 1
> and trim(HICN_NBR)='15248461314T';
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
