How could I remove duplicate rows in SparkSql？

Question

My data is like this:

Code	Time	Total Value	Model Type	First Status	Second Status
11111	07/06/2022 06:45:42	23456	MXJ	Turn On	Turn Off
11111	07/06/2022 06:45:42	23456	MXJ	Turn On	Turn Off
11111	03/02/2022 08:01:11	78231	MXJ	Turn On	Turn Off
22222	04/03/2022 13:23:54	20134	MXJ	Turn On	Turn Off
22222	04/03/2022 13:23:54	20134	MXJ	Turn On	Turn Off

The result I Want:

Code	Time	Total Value	Model Type	First Status	Second Status
11111	07/06/2022 06:45:42	23456	MXJ	Turn On	Turn Off
11111	03/02/2022 08:01:11	78231	MXJ	Turn On	Turn Off
22222	04/03/2022 13:23:54	20134	MXJ	Turn On	Turn Off

My code is like this:

select * from 
(
  select
     code,
     Time,
     Model Type,
     Total Value,
     First Status,
     lead(First Status, 1, null) over(partition by code order by Time asc) as Second Status
  from file
  where Model Type = 'MXJ'
) t 
where First Status='Turn On' and Second='Turn Off'
limit 5

Does this answer your question? [How to delete duplicate rows in SQL Server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) — robere2, Feb 06 '23 at 04:01

score 0 · Accepted Answer · answered Feb 06 '23 at 04:08

The data in your questions is not very clear. However, there are two methods that come to mind in de-duplicating data.

The first is to use DISTINCT. So, if you want to remove duplicates based on all of your columns, you can do,

SELECT DISTINCT *
FROM <your_table>

If you want it to be based on a few columns,

SELECT DISTINCT <column_1>, <column_2> ..
FROM <your_table>

The other option is to use GROUP BY with HAVING. You can group by the columns that you want to de-duplicate based on and then filter out rows with a count greater than 1,

SELECT <column_1>, <column_2> ..
FROM <your_table>
GROUP BY <column_1>, <column_2> ..
HAVING COUNT(*) > 1

So, for your situation, I would suggest creating a TEMP VIEW using the query you have already and then applying one of the methods given above,

CREATE OR REPLACE TEMP VIEW tmp
AS
select
  code,
  Time,
  Model Type,
  Total Value,
  First Status,
  lead(First Status, 1, null) over(partition by code order by Time asc) as Second Status
from file
where Model Type = 'MXJ'
    ) t 
where First Status='Turn On' and Second='Turn Off'

SELECT DISTINCT *
FROM tmp

How could I remove duplicate rows in SparkSql？

1 Answers1