title: Spark MLlib - SQLContext - Group items based on top 3 value
body: <p>I'm trying to make some Basket Market Analysis using Spark MLlib with this dataset:</p>
  
  <pre><code>Purchase_ID Category    Furnisher   Value
  1   ,   A   ,   1   ,   7
  1   ,   B   ,   2   ,   7
  2   ,   A   ,   1   ,   1
  3   ,   C   ,   2   ,   4
  3   ,   A   ,   1   ,   4
  3   ,   D   ,   3   ,   4
  4   ,   D   ,   3   ,   10
  4   ,   A   ,   1   ,   10
  5   ,   E   ,   1   ,   8
  5   ,   B   ,   3   ,   8
  5   ,   A   ,   1   ,   8
  6   ,   A   ,   1   ,   3
  6   ,   B   ,   1   ,   3
  6   ,   C   ,   5   ,   3
  7   ,   D   ,   3   ,   4
  7   ,   A   ,   1   ,   4
  </code></pre>
  
  <p>The transaction value (value) is grouped by each Purchase_ID. And what I want is just return the top 3 categories ​​with higher Value. Basically, I want to return this dataset:</p>
  
  <pre><code>D,A
  E,B,A
  A,B
  </code></pre>
  
  <p>For that I'm trying with the following code:</p>
  
  <pre><code>val data = sc.textFile("PATH");
  
  case class Transactions(Purchase_ID:String,Category:String,Furnisher:String,Value:String);
  
  def csvToMyClass(line: String) = {
  val split = line.split(',');
  Transactions(split(0),split(1),split(2),split(3))}
  
  val df = data.map(csvToMyClass)
          .toDF("Purchase_ID","Category","Furnisher","Value")
          .(select("Purchase_ID","Category") FROM (SELECT "Purchase_ID","Category",dense_rank() over (PARTITION BY "Category" ORDER BY "Value" DESC) as rank) tmp WHERE rank &lt;= 3)
          .distinct();
  </code></pre>
  
  <p>The rank function isn't correct...</p>
  
  <p>Anyone knows how to solve this problem?</p>
  
  <p>Many thanks!</p>