CODEWITHSUNDEEP
scaladataframeapache-sparkdistinct
Haha
Haha

Reputation: 1009

Get distinct rows based on one column

+---------------+---------+-----------------+-------+-------------------+-----------+--------------------+--------------------+---------------+-------+-------------------+-------------------+
|ID_NOTIFICATION|ID_ENTITE|ID_ENTITE_GARANTE|CD_ETAT|DT_ETAT            |CD_ANOMALIE|CD_TYPE_DESTINATAIRE|CD_TYPE_EVENEMENT   |CD_SYS_APPELANT|TYP_MVT|DT_DEBUT           |DT_FIN             |
+---------------+---------+-----------------+-------+-------------------+-----------+--------------------+--------------------+---------------+-------+-------------------+-------------------+
|3110305        |GNE      |GNE              |AT     |2019-06-12 00:03:14|null       |null                |REL_CP_ULTIME_PAPIER|SIGMA          |C      |2019-06-12 00:03:22|2019-06-12 00:03:32|
|3110305        |GNE      |GNE              |AN     |2019-06-12 00:03:28|017        |IDGRC               |REL_CP_ULTIME_PAPIER|SIGMA          |M      |2019-06-12 00:03:22|2019-06-12 15:08:43|
|3110305        |GNE      |GNE              |AN     |2019-06-12 00:03:28|017        |IDGRC               |REL_CP_ULTIME_PAPIER|SIGMA          |M      |2019-06-12 00:03:22|2019-06-12 15:10:06|
|3110305        |GNE      |GNE              |AN     |2019-06-12 15:10:02|017        |IDGRC               |REL_CP_ULTIME_PAPIER|SIGMA          |M      |2019-06-12 00:03:22|2019-06-12 15:10:51|
|3110305        |GNE      |GNE              |AN     |2019-06-12 15:10:02|017        |IDGRC               |REL_CP_ULTIME_PAPIER|SIGMA          |M      |2019-06-12 00:03:22|2019-06-12 15:11:35|

Is there a way to get one row of each distinct CD_ETAT column? In this case it would be the two first rows.

Something similar to this SQL solution but in Scala using DF functions please. Thank you

Upvotes: 0

Views: 1330

Answers (1)

koiralo
koiralo

Reputation: 23109

You can window function with partitionBy CD_ETAT and choose orderBy to get the first one 

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val window = Window.partitionBy("CD_ETAT").orderBy("DT_ETAT")

df.withColumn("row_num", row_number().over(window))
  .filter($"row_num" === 1)
  .drop("row_num")

Output:

+---------------+---------+-----------------+-------+-------------------+-----------+--------------------+--------------------+---------------+-------+-------------------+-------------------+
|ID_NOTIFICATION|ID_ENTITE|ID_ENTITE_GARANTE|CD_ETAT|            DT_ETAT|CD_ANOMALIE|CD_TYPE_DESTINATAIRE|   CD_TYPE_EVENEMENT|CD_SYS_APPELANT|TYP_MVT|           DT_DEBUT|             DT_FIN|
+---------------+---------+-----------------+-------+-------------------+-----------+--------------------+--------------------+---------------+-------+-------------------+-------------------+
|        3110305|      GNE|              GNE|     AT|2019-06-12 00:03:14|       null|                null|REL_CP_ULTIME_PAPIER|          SIGMA|      C|2019-06-12 00:03:22|2019-06-12 00:03:32|
|        3110305|      GNE|              GNE|     AN|2019-06-12 00:03:28|        017|               IDGRC|REL_CP_ULTIME_PAPIER|          SIGMA|      M|2019-06-12 00:03:22|2019-06-12 15:08:43|
+---------------+---------+-----------------+-------+-------------------+-----------+--------------------+--------------------+---------------+-------+-------------------+-------------------+

Upvotes: 3

Related Questions