title: Apache PIG - GROUP BY
body: <p>I am looking to achieve the below functionality in Pig. I have a set of sample records like this.</p>
  
  <p><a href="https://i.stack.imgur.com/Eia18.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Eia18.png" alt="enter image description here"></a></p>
  
  <p>Note that the EffectiveDate column is sometimes blank and also different for the same CustomerID. </p>
  
  <p>Now, as output, I want one record per CustomerID where the EffectiveDate is the MAX. So, for the above example, i want the records highlighted as shown below.</p>
  
  <p><a href="https://i.stack.imgur.com/y2uOo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/y2uOo.png" alt="enter image description here"></a></p>
  
  <p>The way I am doing it currently using PIG is this:</p>
  
  <pre><code>customerdata = LOAD 'customerdata' AS (CustomerID:chararray, CustomerName:chararray, Age:int, Gender:chararray, EffectiveDate:chararray);
  
  --Group customer data by CustomerID
  customerdata_grpd = GROUP customerdata BY CustomerID;
  
  --From the grouped data, generate one record per CustomerID that has the maximum EffectiveDate.
  customerdata_maxdate = FOREACH customerdata_grpd GENERATE group as CustID, MAX(customerdata.EffectiveDate) as MaxDate;
  
  --Join the above with the original data so that we get the other details like CustomerName, Age etc.
  joinwithoriginal = JOIN customerdata by (CustomerID, EffectiveDate), customerdata_maxdate by (CustID, MaxDate);
  
  finaloutput = FOREACH joinwithoriginal GENERATE customerdata::CustomerID as CustomerID, CustomerName as CustomerName, Age as Age, Gender as gender, EffectiveDate as EffectiveDate;
  </code></pre>
  
  <p>I am basically grouping the original data to find the record with the maximum EffectiveDate. Then I join these 'grouped' records with the Original dataset again to get that same record with Max Effective date, but this time I will also get additional data like CustomerName, Age and Gender. This dataset is huge, so this approach is taking a lot of time. Is there a better approach?</p>