Create a new column based on a condition with Spark SQL?

Question

Closed. This question needs details or clarity. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65950592/create-a-new-column-based-on-a-condition-with-spark-sql

Answer 1

SQL tables represent unordered sets (technically multi-sets because they can have duplicates). There is no ordering without a column that specifies the ordering. There is no "last" row, because there is no ordering.

Often, such an ordering is available as an id or an insertion timestamp or something else. If you have such a column, you can enumerate the rows for each no and then add the label:

select t.*,
       (case when row_number() over (partition by type, no order by <ordering col> desc) = 1
             then 'done'
        end) as flag
from t;

Note: This guarantees that exactly one row with no/type has "done". This is true even if the rows are interleaved (based on the ordering column). If you just want to check if the next row is different, then you can use lead() instead.