Search code examples
apache-drill

How to use arithmetic operators in apache drill query where clause?

I am trying to query a parquet file using Apache Drill. I want to sort the values and get the largest value of FloatCol1. So the following query works.

select * from dfs.tmp.`tmp.parquet` order by FloatCol1 desc limit 1;

I also have another column FloatCol2 and I want to sort again using the ratio of FloatCol1/FloatCol2. So I did the following queries to try which will work but NONE did.

select * from dfs.tmp.`tmp.parquet` order by (FloatCol1/FloatCol2) desc limit 1;
select * from dfs.tmp.`tmp.parquet` order by (FloatCol1/FloatCol2) as ratio desc limit 1;
select * from dfs.tmp.`tmp.parquet` order by FloatCol1/FloatCol2 desc limit 1;

Can this kind of query be done? If yes, how?

Solution

  • Got it now. I just have to make it like this

    select * from dfs.tmp.`tmp.parquet` order by cast(FloatCol1 as decimal(28, 2))/cast(FloatCol2 as decimal(28, 2)) desc limit 1;

    I have to cast each individual value before applying the operator.