CODEWITHSUNDEEP
sqlhadoopapache-hive
dedpo
dedpo

Reputation: 502

trying to find the max of select statement in HIVE

I am trying to yield a the top person by weight in the below script. I have a working version way below which returns Matt Holiday with 250 as weight, and now that is all i want The player with Max weight and him only not anyone else

SELECT DISTINCT n.fname, n.lname, MAX(n.weight) FROM master n 
JOIN (SELECT b.id as id, b.year as year, b.triples as triples FROM batting b 
WHERE year == 2005 AND triples > 5) x
ON x.id = n.id
ORDER BY n.weight DESC;

now this comes up with an error like this 

Failed: Semantic Exception [Error 10128]: Line 4:34 Not yet supported place for UDAF 'MAX'

However this script returns what i expected, output below

SELECT DISTINCT n.fname, n.lname, n.weight FROM master n 
JOIN (SELECT b.id as id, b.year as year, b.triples as triples FROM batting b 
WHERE year == 2005 AND triples > 5) x
ON x.id = n.id
ORDER BY n.weight DESC;

output

Matt Holiday 250
Bill Dickey 205
Bob Feller 200
Tom Glavine 190

Upvotes: 0

Views: 3217

Answers (1)

Burak Karasoy
Burak Karasoy

Reputation: 1690

you have an aggregation function and in order to get the result you want you need to use group by 

SELECT n.fname, n.lname, MAX(n.weight) FROM master n 
JOIN (SELECT b.id as id, b.year as year, b.triples as triples FROM batting b 
WHERE year == 2005 AND triples > 5) x
ON x.id = n.id
GROUP BY n.fname,n.lname
ORDER BY n.weight DESC
LIMIT 1;

Parameters or Arguments

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

expression1, expression2, ... expression_n Expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause at the end of the SQL statement http://www.techonthenet.com/sql/group_by.php

this is probably because the same rule exist in HiveQL as well

Upvotes: 1

Related Questions