Group by and order by in Spark SQL

Question

I am trying to access s3 data using a spark Application. I am applying Spark SQL to retrieve the data. It is not taking group by clause.

DataFrame summaryQuery=sql.sql("Select score from summary order by updationDate desc);
summaryQuery.groupBy("sessionId").count().show();
summaryQuery.show();

Also i am trying it directly

    DataFrame summaryQuery=sql.sql("Select score from summary group by sessionId order by updationDate desc);
summaryquery.show();

But in both the cases i am getting SQL exception.

Exception in thread "main" org.apache.spark.sql.AnalysisException: expression 'score' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get.;

Please specify how can i query the data.

Answer 1

In Spark SQL, We have to wrap it in a function either first(column_name) or last(column_name) or any aggregate function when column_name is not present in group by clause. It will take either first or last value from the fetched rows respectively.

DataFrame summaryQuery=sql.sql("Select first(score) from summary group by sessionId order by updationDate desc);
summaryquery.show();