ql/src/test/queries/clientpositive/windowing_rank.q

drop table over10k_n10;

create table over10k_n10(
           t tinyint,
           si smallint,
           i int,
           b bigint,
           f float,
           d double,
           bo boolean,
           s string,
	   ts timestamp, 
           `dec` decimal(4,2),  
           bin binary)
       row format delimited
       fields terminated by '|';

load data local inpath '../../data/files/over10k' into table over10k_n10;

select s, rank() over (partition by f order by t) from over10k_n10 limit 100;

select s, dense_rank() over (partition by ts order by i,s desc) from over10k_n10 limit 100;

select s, cume_dist() over (partition by bo order by b,s) from over10k_n10 limit 100;

select s, percent_rank() over (partition by `dec` order by f) from over10k_n10 limit 100;

-- If following tests fail, look for the comments in class PTFPPD::process()

select ts, `dec`, rnk
from
  (select ts, `dec`,
          rank() over (partition by ts order by `dec`)  as rnk
          from
            (select other.ts, other.`dec`
             from over10k_n10 other
             join over10k_n10 on (other.b = over10k_n10.b)
            ) joined
  ) ranked
where rnk =  1 limit 10;

select ts, `dec`, rnk
from
  (select ts, `dec`,
          rank() over (partition by ts)  as rnk
          from
            (select other.ts, other.`dec`
             from over10k_n10 other
             join over10k_n10 on (other.b = over10k_n10.b)
            ) joined
  ) ranked
where `dec` = 89.5 limit 10;

select ts, `dec`, rnk
from
  (select ts, `dec`,
          rank() over (partition by ts order by `dec`)  as rnk
          from
            (select other.ts, other.`dec`
             from over10k_n10 other
             join over10k_n10 on (other.b = over10k_n10.b)
             where other.t < 10
            ) joined
  ) ranked
where rnk = 1 limit 10;