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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
