spark-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ayan guha <guha.a...@gmail.com>
Subject Re: How to filter based on a constant value
Date Sun, 31 Jul 2016 00:20:09 GMT
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.talebzadeh@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

Mime
View raw message