How to select top row from spark sql join

user_1511294 0
Viewed 536

I have 2 dataframes like this. df1

+----+-------------+
|colA|colB         |
+----+-------------+
|   1|   "someval" | 
|   2|   "someval2"|  
|   3|   "someval3"|

df2

+----+-------------+
|colA|colC         |
+----+-------------+
|   1|   "someval" | 
|   1|   "someval2"|  
|   2|   "someval3"|

if I do inner join df1 and df2 (via colA) I get this .

+----+-------------+----------+
|colA|colB         |colC      |
+----+-------------+----------+
|   1|   "someval" |"someval" |
|   1|   "someval" |"someval2"| 
|   2|   "someval2"|"someval3"|

But I want only the distinct row of colA (So, fetching the top row of colA will suffice)

+----+-------------+----------+
|colA|colB         |colC      |
+----+-------------+----------+
|   1|   "someval" |"someval" |
|   2|   "someval2"|"someval3"|
apache-spark-sql
2 Answers

Try this.

distinct_df = df2.dropDuplicates(['colA'])

Joining your dataframes

inner_join_df = df1.join(distinct_df, df1.colA == distinct_df.colA)
inner_join_df.show()

I have joined dataframes using pandas:

import pandas as pd
data1 =[[1,'someval'],[2,'someval2'],[3,'someval3']]
data2 =[[1,'someval'],[1,'someval2'],[2,'someval3']]

df1=pd.DataFrame(data1,columns=['colA','colB'])
df2=pd.DataFrame(data2,columns=['colA','colC'])

unique_df=df2.drop_duplicates('colA')

joindf = pd.merge(df1,unique_df,on='colA',how='inner')
print(joindf )
user_103166890

use window function to sort rows with same value of column A as you prefer using other columns .In second step filter only rows with function result 1

sqlContext.sql(""" select colA,colB,colC from ( SELECT *,row_number() over (PARTITION by colA order by colB,colC) as rn from df_p )x where rn=1 """ ).show(60)

user_79806970
Related