In Hiveql, SemanticException [Error 10004]: Line 1:126 Invalid table alias or column reference 'timesheet': (possible column names are: author)

1
votes

My query is following this

select drivers.Author 
from timesheet 
join drivers 
on drivers.BibNum = timesheet.BibNum
group by drivers.Author 
order by count(timesheet.BibNum) desc
limit 1;

But I got this error,

Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 1:126 Invalid table alias or column reference 'timesheet': (possible column names are: author)

My table looks like this

Drivers

+-----------------+-----------------+-----------------+--+
| bibnum          | string          | from deserializer |
| title           | string          | from deserializer |
| author          | string          | from deserializer |
| isbn            | string          | from deserializer |
| publicationyear | string          | from deserializer |
| publisher       | string          | from deserializer |
| subjects        | string          | from deserializer |
| itemtype        | string          | from deserializer |
| itemcollection  | string          | from deserializer |
| floatingitem    | string          | from deserializer |
| itemlocation    | string          | from deserializer |
| reportdate      | string          | from deserializer |
| itemcount       | string          | from deserializer |
+-----------------+-----------------+-----------------+--+

timesheet

+-----------------+-----------------+-----------------+--+
| bibnum          | string          | from deserializer |
| itembarcode     | string          | from deserializer |
| itemtype        | string          | from deserializer |
| itemcollection  | string          | from deserializer |
| callnumber      | string          | from deserializer |
| checkoutdatetime | string          | from deserializer |
+-----------------+-----------------+-----------------+--+

If you know the reason, please enlighten me and thank you for your help. If you need more information to my schema, let me know.

2
hivehiveql
i put order by DESC since you want most popular author. - jose_bacoy

2 Answers

1
votes

The error is with order by. Only selected columns can go in there. Change the query to remove order by. Or add count to select and use it for ordering.

select drivers.Author,count(timesheet.BibNum) as cnt 
from timesheet 
join drivers on drivers.BibNum = timesheet.BibNum
group by drivers.Author 
order by cnt
1
votes

Join the two tables by bibnum then group by aurhor name. the most popular author will have most number of readers, so count them in descending order. Limit 1 will get only the first record on top.

EDIT: use HIVE instead of mysql

SELECT tab.Author
from (
SELECT t2.Author, 
      count(t1.Bibnum) as cnt
FROM  timesheet t1
INNER JOIN drivers t2 on trim( t1.BibNum) = trim(t2.Bibnum)
WHERE LENGTH(trim(t2.Author)) > 0
GROUP BY t2.Author
ORDER BY cnt DESC 
LIMIT 1) tab;