title: Second highest value by department using Apache Spark DataFrame
body: <p>I have a table where <code>department</code> and <code>value</code> is available now if we will use SQL query to achieve the output of second highest value by each department then we write like:</p>
  
  <pre><code>select * from (SELECT department, asset_value, DENSE_RANK() over (partition by DEPARTMENT order by ASSET_VALUE) as x from [User1].[dbo].[UsersRecord]) y where x = 2;
  </code></pre>
  
  <p>But, in Apache Spark, if we have a restriction to not to use the SparkSQL and we have to achieve the output using DataFrame only then how we should write the Scala code?</p>
  
  <p>I tried from my end by taking the help of the Documentation but could not figure it out.</p>
  
  <p>My Code:</p>
  
  <pre><code>package com.tg.testpack1
  
  import org.apache.spark.sql.expressions.Window
  import org.apache.spark.sql.functions.row_number
  import org.apache.spark.SparkConf
  import org.apache.spark.SparkContext
  import org.apache.spark.sql.SQLContext
  import org.apache.spark.sql._
  import org.apache.spark.sql.functions._
  
  object class2
  {
   def main(args: Array[String]) 
   {
      val sparksessionobject = SparkSession.builder()
        .master("local")
        .config("spark.sql.warehouse.dir", "C:/Users/ox/spark/spark/spark-warehouse")
        .getOrCreate()
      sparksessionobject.conf.set("spark.sql.shuffle.partitions", 4)
      sparksessionobject.conf.set("spark.executor.memory", "2g")
      import sparksessionobject.sqlContext.implicits._
  
  val df = Seq(("DEPT1", 1000), ("DEPT1", 500), ("DEPT1", 700), ("DEPT2", 400), ("DEPT2", 200),  ("DEPT3", 500), ("DEPT3", 200))
           .toDF("department", "assetValue")
  
  df.withColumn("col3", dense_rank().over(Window.partitionBy($"department").orderBy($"assetValue".desc))).show
  }
  }
  </code></pre>
  
  <p>I am getting the output:</p>
  
  <pre><code>+-------------+----------+----+
  |accountNumber|assetValue|col3|
  +-------------+----------+----+
  |        DEPT3|       500|   1|
  |        DEPT3|       200|   2|
  |        DEPT1|      1000|   1|
  |        DEPT1|       700|   2|
  |        DEPT1|       500|   3|
  |        DEPT2|       400|   1|
  |        DEPT2|       200|   2|
  +-------------+----------+----+
  </code></pre>
  
  <p>I am expecting the output:</p>
  
  <pre><code>+-------------+----------+----+
  |accountNumber|assetValue|col3|
  +-------------+----------+----+
  |        DEPT1|       700|   2|
  |        DEPT2|       200|   2|
  |        DEPT3|       200|   2|
  +-------------+----------+----+
  </code></pre>