title: Find TOP 10 latest record for each BUYER_ID for yesterday's date
body: <p>This is the below table</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS TestingTable1 
  ( 
  BUYER_ID BIGINT,
  ITEM_ID BIGINT, 
  CREATED_TIME STRING
  )
  </code></pre>
  
  <p>And this is the below data in the above table-</p>
  
  <pre><code>BUYER_ID    |    ITEM_ID       |    CREATED_TIME
  ------------+------------------+-----------------------
  1015826235      220003038067        2012-07-09 19:40:21,
  1015826235      300003861266        2012-07-09 18:19:59,
  1015826235      140002997245        2012-07-09 09:23:17,
  1015826235      210002448035        2012-07-09 22:21:11,
  1015826235      260003553381        2012-07-09 07:09:56,
  1015826235      260003553382        2012-07-09 19:40:39,
  1015826235      260003553383        2012-07-09 06:58:47,
  1015826235      260003553384        2012-07-09 07:28:47,
  1015826235      260003553385        2012-07-09 08:48:47,
  1015826235      260003553386        2012-07-09 06:38:47,
  1015826235      260003553387        2012-07-09 05:38:47,
  1015826235      260003553388        2012-07-09 04:55:47,
  1015826235      260003553389        2012-07-09 06:54:37,
  34512201        597245693           2012-07-09 16:20:21,
  34512201        8071787728          2012-07-09 15:19:59,
  34512201        5868222883          2012-07-09 08:23:17,
  34512201        2412180494          2012-07-09 22:21:11,
  34512201        2422054205          2012-07-09 06:09:56,
  34512201        1875744030          2012-07-09 19:40:39,
  34512201        5639158173          2012-07-09 06:58:47,
  34512201        5656232360          2012-07-09 07:28:47, 
  34512201        959188449           2012-07-09 08:48:47,
  34512201        4645350592          2012-07-09 06:38:47,
  34512201        5657320532          2012-07-09 05:38:47,
  34512201        290419656539        2012-07-09 04:55:47,
  </code></pre>
  
  <p>If you see the above data in the table, there are only two UNIQUE <code>BUYER_ID</code> and corresponding to those I have <code>ITEM_ID</code> AND <code>CREATED_TIME</code>. I need only 10 latest record basis on the time for the day before today's date whenever I will be firing this query (meaning yesterday's date) for each <code>BUYER_ID</code>.</p>
  
  <p>So for this <code>BUYER_ID</code> - <code>34512201</code> I need 10 latest record for each <code>BUYER_ID</code> basis on <code>CREATED_TIME</code> for yesterday's date only.</p>
  
  <p>And each <code>BUYER_ID</code> can have any day's data. But I am specifically interested for day before  today's data(means yesterday's date always) by checking at the <code>CREATED_TIME</code></p>
  
  <p><strong>Find <code>TOP 10</code> latest data for each <code>BUYER_ID</code> for yesterday's date. Below is the sample output I should be getting corresponding to each <code>BUYER_ID</code>.</strong></p>
  
  <p>Sample Output.</p>
  
  <pre><code>BUYER_ID    |    ITEM_ID       |    CREATED_TIME
  ------------+------------------+-----------------------
  34512201        2412180494          2012-07-09 22:21:11
  34512201        1875744030          2012-07-09 19:40:39
  34512201        597245693           2012-07-09 16:20:21
  34512201        8071787728          2012-07-09 15:19:59
  34512201        959188449           2012-07-09 08:48:47
  34512201        5868222883          2012-07-09 08:23:17
  34512201        5656232360          2012-07-09 07:28:47 
  34512201        5639158173          2012-07-09 06:58:47
  34512201        4645350592          2012-07-09 06:38:47
  34512201        2422054205          2012-07-09 06:09:56
  1015826235      210002448035        2012-07-09 22:21:11
  1015826235      260003553382        2012-07-09 19:40:39
  1015826235      220003038067        2012-07-09 19:40:21
  1015826235      300003861266        2012-07-09 18:19:59
  1015826235      140002997245        2012-07-09 09:23:17
  1015826235      260003553385        2012-07-09 08:48:47
  1015826235      260003553384        2012-07-09 07:28:47
  1015826235      260003553381        2012-07-09 07:09:56
  1015826235      260003553383        2012-07-09 06:58:47
  1015826235      260003553389        2012-07-09 06:54:37
  </code></pre>
  
  <p>I am working with <code>Hive</code> and <code>Hive</code> supports SQL like syntax. So I need to make sure the SQL should work in Hive environment too.</p>
  
  <p>Can anyone help me with this?</p>
  
  <p><strong>Update:-</strong></p>
  
  <p>I am using the below query and I need to get top 10 latest from the below query and need to add one more qualifier for date check, means in <code>where clause for yesterday's date</code>- I cannot use <code>TOP 10</code> here as Hive doesn't support <code>TOP 10</code> sql syntax. I need some other way to do this problem.</p>
  
  <pre><code>SELECT * FROM TestingTable1 WHERE ORDER BY buyer_id, created_time DESC;
  </code></pre>
  
  <p><strong>One More UPDATE:-</strong></p>
  
  <p>I wrote this below query with the use of <code>RANK UserDefinedFunction</code>.</p>
  
  <pre><code>SELECT buyer_id, item_id, created_time, rk
  FROM (
      SELECT buyer_id, item_id, rank(item_id) as rk, created_time
      FROM testingtable1
      DISTRIBUTE BY buyer_id, item_id
      SORT BY buyer_id, item_id, created_time desc
  ) a 
  WHERE rk &lt; 10
  ORDER BY buyer_id, created_time, rk;
  </code></pre>
  
  <p>And this is the RANK UDF functions in Java-</p>
  
  <pre><code>package com.example.hive.udf;
  import org.apache.hadoop.hive.ql.exec.UDF;
  
      public final class Rank extends UDF{
          private int  counter;
          private String last_key;
          public int evaluate(final String key){
            if ( !key.equalsIgnoreCase(this.last_key) ) {
               this.counter = 0;
               this.last_key = key;
            }
            return this.counter++;
          }
      }
  </code></pre>
  
  <p>And above query is not work the way I wanted to, some sort of twist has to be made I guess in that query.</p>
  
  <p><strong>Is there any way to do this without using any UDF in HiveQL?</strong></p>