title: Compare 2 dataframes and filter results based on date column in spark
body: <p>I have 2 dataframes in spark as mentioned below.</p>
  
  <pre><code>val test = hivecontext.sql("select max(test_dt) as test_dt from abc");
  
  test: org.apache.spark.sql.DataFrame = [test_dt: string]
  
  val test1 = hivecontext.table("testing");
  </code></pre>
  
  <p>where test1 has columns like id,name,age,audit_dt</p>
  
  <p>I want to compare these 2 dataframes and filter rows from test1 where audit_dt > test_dt. Somehow I am not able to do that. I am able to compare audit_dt with literal date using lit function but i am not able to compare it with another dataframe column.</p>
  
  <p>I am able to compare literal date using lit function as mentioned below</p>
  
  <pre><code>val output = test1.filter(to_date(test1("audit_date")).gt(lit("2017-03-23")))
  </code></pre>
  
  <p>Max Date in test dataframe is -> 2017-04-26</p>
  
  <p>Data in test1 Dataframe -></p>
  
  <pre><code>Id,Name,Age,Audit_Dt
  1,Rahul,23,2017-04-26
  2,Ankit,25,2017-04-26
  3,Pradeep,28,2017-04-27
  </code></pre>
  
  <p>I just need the data for Id=3 since that only row qualifies the greater than criteria of max date.</p>
  
  <p>I have already tried below mentioned option but it is not working.</p>
  
  <pre><code>val test = hivecontext.sql("select max(test_dt) as test_dt from abc")
  val MAX_AUDIT_DT = test.first().toString()
  val output = test.filter(to_date(test("audit_date")).gt((lit(MAX_AUDIT_DT))))
  </code></pre>
  
  <p>Can anyone suggest as way to compare it with column of dataframe test?</p>
  
  <p>Thanks</p>