Hi Mitch, I think you were missing a step: [your result] maxdate: org.apache.spark.sql.Row = [2015-12-15] Since maxdate is of type Row, you would want to extract the first column of the Row with:
>> val maxdateStr = maxdate.getString(0) assuming the column type is String. API doc is here: http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.Row Then you can do the query: >> col("transactiondate") === maxdateStr Xinh On Sat, Jul 30, 2016 at 5:20 PM, ayan guha <guha.a...@gmail.com> wrote: > select * > from (select *, > rank() over (order by transactiondate) r > from ll_18740868 where transactiondescription='XYZ' > ) inner > where r=1 > > Hi Mitch, > > If using SQL is fine, you can try the code above. You need to register > ll_18740868 as temp table. > > On Sun, Jul 31, 2016 at 6:49 AM, Mich Talebzadeh < > mich.talebza...@gmail.com> wrote: > >> >> Hi, >> >> I would like to find out when it was the last time I paid a company with >> Debit Card >> >> >> This is the way I do it. >> >> 1) Find the date when I paid last >> 2) Find the rest of details from the row(s) >> >> So >> >> var HASHTAG = "XYZ" >> scala> var maxdate = >> ll_18740868.filter(col("transactiondescription").contains(HASHTAG)).agg(max("transactiondate")).collect.apply(0) >> maxdate: org.apache.spark.sql.Row = [2015-12-15] >> >> OK so it was 2015-12-15 >> >> >> Now I want to get the rest of the columns. This one works when I hard >> code the maxdate! >> >> >> scala> ll_18740868.filter(col("transactiondescription").contains(HASHTAG) >> && col("transactiondate") === "2015-12-15").select("transactiondate", >> "transactiondescription", "debitamount").show >> +---------------+----------------------+-----------+ >> |transactiondate|transactiondescription|debitamount| >> +---------------+----------------------+-----------+ >> | 2015-12-15| XYZ LTD CD 4636 | 10.95| >> +---------------+----------------------+-----------+ >> >> Now if I want to use the var maxdate in place of "2015-12-15", how would >> I do that? >> >> I tried lit(maxdate) etc but they are all giving me error? >> >> java.lang.RuntimeException: Unsupported literal type class >> org.apache.spark.sql.catalyst.expressions.GenericRowWithSchema >> [2015-12-15] >> >> >> Thanks >> > > > > -- > Best Regards, > Ayan Guha >
