title: using alias for cast in HIVE
body: <p>I have a table called loan with loan amount,annual income, year (MMM-YY format) and member id. I am trying to find the highest loan amount in a year along wit annual income and member id details.</p>
  
  <p>I tried to group the highest loan amount by year using the code</p>
  
  <pre><code>select max(cast(loan_amt as int)),issue_d from loan group by issue_d;
  </code></pre>
  
  <p>then I wanted also to fetch the member id and annual income information so I wrote the following code</p>
  
  <p>but it is giving me error message for using alias for a column which is cast.</p>
  
  <p>Code:</p>
  
  <pre><code>select a.loan_amt,a.member_id,a.annual_inc,a.issue_d
  from 
  (select loan_amt,member_id,annual_inc,issue_d from loan) a
  join
  (select max(cast(loan_amt as int)) as ml,issue_d from loan group by issue_d) c
  where ((a.issue_d=c.issue_d) and (a.loan_amt=a.ml));
  </code></pre>