0

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
dedpo
  • 482
  • 11
  • 30

1 Answers1

1

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

Burak Karasoy
  • 1,682
  • 2
  • 21
  • 33
  • you can't have select distinct and Group by in the same query? – dedpo Apr 18 '16 at 18:22
  • no, if you are using aggregation functions(avg,max,min ..) and group by in a query you need to add other columns in group by.for example select a ,b,max(c) from table1 group by(a,b) you have to add a and b to group by. You can use distinct, it just declares that only unique results needed. – Burak Karasoy Apr 18 '16 at 18:30
  • this is what i get after fixing your script (f.name) should be n.fname. and this is the error Failed: ParseException missing ) at ',' near 'lname' line 8:25 missing EOF at ')' near 'lname' – dedpo Apr 18 '16 at 18:44
  • @dedpo delete brackets near the columns which are writtten into group by. It looks like just a syntax error. – Burak Karasoy Apr 18 '16 at 18:56
  • I did remove them and then it gives SELECT DISNTCT AND GROUP BY can not be in the same query Error encountered near token 'lname' – dedpo Apr 18 '16 at 19:06
  • sry about if I meant something wrong. I have never used distinct and group by together. Both are used for similar purposes using them together already makes no sense. – Burak Karasoy Apr 18 '16 at 19:11
  • Any other solutions? – dedpo Apr 18 '16 at 19:27
  • @dedpo query I wrote should work without DISTINCT.? isn't it working? – Burak Karasoy Apr 18 '16 at 19:33
  • yes but how do i get only the first record, i only need to select Matt Holiday, not everyone who fits the crietera, the HEAVIEST player is Matt Holiday and we should only get that – dedpo Apr 18 '16 at 19:50
  • yes that should would, I totaly forgot about the LIMIT function :/ – dedpo Apr 18 '16 at 20:08