title: group by and filter highest value in data frame in scala
body: <p>I have some data like this:</p>
  
  <pre><code>a,timestamp,list,rid,sbid,avgvalue
  1,1011,1001,4,4,1.20
  2,1000,819,2,3,2.40
  1,1011,107,1,3,5.40
  1,1021,819,1,1,2.10
  </code></pre>
  
  <p>In the data above I want to find which stamp has the highest tag value (avg. value) based on the tag. Like this.</p>
  
  <p>For time stamp 1011 and a 1:</p>
  
  <pre><code>1,1011,1001,4,4,1.20
  1,1011,107,1,3,5.40
  </code></pre>
  
  <p>The output would be:</p>
  
  <pre><code>1,1011,107,1,3,5.40  //because for timestamp 1011 and tag 1 the higest avg value is 5.40
  </code></pre>
  
  <p>So I need to pick this column.</p>
  
  <p>I tried this statement, but still it does not work properly:</p>
  
  <pre><code>val highvaluetable = df.registerTempTable("high_value")
  val highvalue = sqlContext.sql("select a,timestamp,list,rid,sbid,avgvalue  from high_value")   highvalue.select($"a",$"timestamp",$"list",$"rid",$"sbid",$"avgvalue".cast(IntegerType).as("higher_value")).groupBy("a","timestamp").max("higher_value")
   highvalue.collect.foreach(println)
  </code></pre>
  
  <p>Any help will be appreciated.</p>
  
  <p>After I applied some of your suggestions, I am still getting duplicates in my data.</p>
  
  <pre><code>+---+----------+----+----+----+----+
  |a| timestamp| list|rid|sbid|avgvalue|
  +---+----------+----+----+----+----+
  |  4|1496745915| 718|   4|   3|0.30|
  |  4|1496745918| 362|   4|   3|0.60|
  |  4|1496745913| 362|   4|   3|0.60|
  |  2|1496745918| 362|   4|   3|0.10|
  |  3|1496745912| 718|   4|   3|0.05|
  |  2|1496745918| 718|   4|   3|0.30|
  |  4|1496745911|1901|   4|   3|0.60|
  |  4|1496745912| 718|   4|   3|0.60|
  |  2|1496745915| 362|   4|   3|0.30|
  |  2|1496745915|1901|   4|   3|0.30|
  |  2|1496745910|1901|   4|   3|0.30|
  |  3|1496745915| 362|   4|   3|0.10|
  |  4|1496745918|3878|   4|   3|0.10|
  |  4|1496745915|1901|   4|   3|0.60|
  |  4|1496745912| 362|   4|   3|0.60|
  |  4|1496745914|1901|   4|   3|0.60|
  |  4|1496745912|3878|   4|   3|0.10|
  |  4|1496745912| 718|   4|   3|0.30|
  |  3|1496745915|3878|   4|   3|0.05|
  |  4|1496745914| 362|   4|   3|0.60|
  +---+----------+----+----+----+----+
  
   4|1496745918| 362|   4|   3|0.60|  
   4|1496745918|3878|   4|   3|0.10|
  </code></pre>
  
  <p>Same time stamp with same tag. This is considered as duplicate.</p>
  
  <p>This is my code:</p>
  
  <pre><code>rdd.createTempView("v1")
  val rdd2=sqlContext.sql("select max(avgvalue) as max from v1 group by  (a,timestamp)")
  rdd2.createTempView("v2")
  val rdd3=sqlContext.sql("select a,timestamp,list,rid,sbid,avgvalue from v1  join v2 on v2.max=v1.avgvalue").show()
  </code></pre>