title: HIVE equivalent of FIRST and LAST
body: <p>I have a table with 3 columns:</p>
  
  <pre><code>table1: ID, CODE, RESULT, RESULT2, RESULT3
  </code></pre>
  
  <p>I have this SAS code:</p>
  
  <pre><code>data table1
     set table1;
     BY ID, CODE;
     IF FIRST.CODE and RESULT='A' THEN OUTPUT;
     ELSE IF LAST.CODE and RESULT NE 'A' THEN OUTPUT;
  RUN;
  </code></pre>
  
  <p>So we are grouping the data by ID and CODE, and then writing to the dataset if certain conditions are met. I want to write a hive query to replicate this. This is what I have:</p>
  
  <pre><code>proc sql;
     create table temp as
     select *, row_number() over (partition by ID, CODE) as rowNum
     from table1;
  
     create table temp2 as
     select a.ID, a.CODE, a.RESULT, a.RESULT2, a.RESULT3
     from temp a
     inner join (select ID, CODE, max(rowNum) as maxRowNum
                 from temp
                 group by ID, CODE) b
       on a.ID=b.ID and a.CODE=b.CODE
     where (a.rowNum=1 and a.RESULT='A') or (a.rowNum=b.maxRowNum and a.RESULT NE 'A');
  quit;
  </code></pre>
  
  <p>There are two issues I see with this. </p>
  
  <p>1) The row that is first or last in each BY group is entirely dependant on the order of rows in table1 in SAS, we aren't ordering by anything. I don't think row order is preserved when translating to a hive query.</p>
  
  <p>2) The SAS code is taking the first row in each BY GROUP or the last, not both. I think that my HIVE query is taking both, resulting in more rows than I want.</p>
  
  <p>Any suggestions or insight on how to improve my query is appreciated. Is it even possible to replicate this SAS code in HIVE?</p>