RaqForum 41 No.
179 View  • 
     ...

SQL, Set different flags for different groups according to whether there are duplicate values

 

In MSSQL, my_table table has three columns (as shown below). Serial_Number is the grouping field; Id and Last_update_date contain detail data, and there are duplicate values in the last field.

Id

Serial_Number

Last_update_date

1

1

3344

07-05-2024

2

2

3344

02-05-2024

3

3

9098

20-02-2024

4

4

9098

20-02-2024

5

5

2323

07-03-2023

6

6

2323

17-10-2023

7

7

2323

17-10-2023

8

8

1894

10-07-2022

9

9

1894

20-02-2024

10

10

1894

02-05-2024

We need to add a computed column named Flag. The rule is like this: Group rows by Serial_Number and record Flag value as "Y" for each record in this group if there are duplicate Last_update_date values; otherwise record Flag value as "N".


Id

Serial_Number

Last_update_date

Flag

1

1

3344

07-05-2024

N

2

2

3344

02-05-2024

N

3

3

9098

20-02-2024

Y

4

4

9098

20-02-2024

Y

5

5

2323

07-03-2023

Y

6

6

2323

17-10-2023

Y

7

7

2323

17-10-2023

Y

8

8

1894

10-07-2022

N

9

9

1894

20-02-2024

N

10

10

1894

02-05-2024

N

Write the following SPL code:

A

1

=mssql1.query("select *,'N'as Flag from my_table")

2

=A1.group@u(Serial_Number).run(if(~.groups(Last_update_date;count(1)).pselect(#2>1),~.run("Y":Flag)))

group()function groups rows without aggregation; @u option retains the original order of the records for the result. groups() function performs grouping and aggregation. ~ represents the current group/member; run()function modifies records in order. pselect() returns positions of members meeting the specified condition.

Source:https://stackoverflow.com/questions/78456365/column-comparison-between-rows-of-the-same-group-used-in-partition-by-clause


 
 •  179 View  •