CODEWITHSUNDEEP
apache-sparkgroup-byapache-spark-sqltop-n
Georg Heiler
Georg Heiler

Reputation: 17676

Spark sql top n per group

How can I get the top-n (lets say top 10 or top 3) per group in spark-sql?

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ provides a tutorial for general SQL. However, spark does not implement subqueries in the where clause.

Upvotes: 10

Views: 12495

Answers (1)

tyagi
tyagi

Reputation: 265

You can use the window function feature that was added in Spark 1.4 Suppose that we have a productRevenue table as shown below. enter image description here

the answer to What are the best-selling and the second best-selling products in every category is as follows

SELECT product,category,revenue FROM 
   (SELECT product,category,revenue,dense_rank() 
         OVER (PARTITION BY category ORDER BY revenue DESC) as rank 
    FROM productRevenue) tmp 
WHERE rank <= 2

Tis will give you the desired result

Upvotes: 10

Related Questions