CODEWITHSUNDEEP
sql
Akshay Kumar
Akshay Kumar

Reputation: 1

I want to find highest row from multiple columns in hive

a b c
1 1 1
2 2 2
3 3 3
4 4 4
5 5 1

here 3 columns are there and i have to find the highest row among these

result would be - 4 4 4

Upvotes: 0

Views: 110

Answers (3)

Akshay Kumar
Akshay Kumar

Reputation: 1

select
    a,b,c,(a+b+c)/3
from
    table
where
    (a+b+c)/3 >= (select (avg(a)+avg(b)+avg(c))/3 from table)
and         a >= (select cast(avg(a) as int) from table)
and         b >= (select cast(avg(b) as int) from table)
and         c >= (select cast(avg(c) as int) from table)

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

OK, so I've passed this round the think tank here and we find it a good puzzle trying to work out what it means. Another proposal is that 551 is the same as 155 so 444 is higher than 155.

We can turn 3,1,2 into 123 with "least number, and middle number, and greatest number". Middle number is given by the sum of all numbers minus the greatest minus the least

In this case:

SELECT * FROM table 
ORDER BY 
  LEAST(a,b,c) DESC,
  a+b+c-GREATEST(a,b,c)-LEAST(a,b,c) DESC,
  GREATEST(a,b,c) DESC 
  LIMIT 1

If your hive doesn't have greatest and least then you'll have to fake it with case when (I recommend you use a subquery)

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74605

If "highest row" means the greatest sum, then it would be

SELECT * FROM table ORDER BY a+b+c DESC LIMIT 1

if I understood you correctly? Not sure what you want to do about duplicates? What if there is a row 3 4 5?

Upvotes: 0

Related Questions