I have Jobs table:
| Job | Title | EndOfMonth |
|---|---|---|
| 36950704 | Senior Full Stack Developer (React Native) | 2022-01-31 |
| 36953479 | Senior Full Stack Developer (React Native) 2 | 2022-01-31 |
| 36953482 | Senior Full Stack Developer (React) 3 | 2022-01-31 |
| 37131847 | Senior Software Developer (.NET Core, Angular) | 2022-03-31 |
| 37132156 | Senior Software Developer (.NET Core, Angular) 2 | 2022-03-31 |
| 37132174 | Senior Software Developer (.NET Core, Angular) 3 | 2022-03-31 |
| 37132177 | Senior Software Developer (.NET Core, Angular) 4 | 2022-03-31 |
| 37309773 | Senior Software Developer (FinTech) | 2022-05-31 |
| 37309830 | Senior Software Developer (FinTech) 2 | 2022-05-31 |
| 37116394 | Senior .NET Developer (Windows Forms) | 2022-03-31 |
In this table, for each EndOfMonth, we have multiple jobs (openings) for a title. Titles do not match exactly, for example for 2022-01-31, there are 3 related jobs (openings)
- Senior Full Stack Developer (React Native)
- Senior Full Stack Developer (React Native) 2
- Senior Full Stack Developer (React) 3
Desired output is this:
| Job | Title | EndOfMonth | Related Job |
|---|---|---|---|
| 36950704 | Senior Full Stack Developer (React Native) | 2022-01-31 | 36950704 |
| 36953479 | Senior Full Stack Developer (React Native) 2 | 2022-01-31 | 36950704 |
| 36953482 | Senior Full Stack Developer (React) 3 | 2022-01-31 | 36950704 |
| 37131847 | Senior Software Developer (.NET Core, Angular) | 2022-03-31 | 37131847 |
| 37132156 | Senior Software Developer (.NET Core, Angular) 2 | 2022-03-31 | 37131847 |
| 37132174 | Senior Software Developer (.NET Core, Angular) 3 | 2022-03-31 | 37131847 |
| 37132177 | Senior Software Developer (.NET Core, Angular) 4 | 2022-03-31 | 37131847 |
| 37309773 | Senior Software Developer (FinTech) | 2022-05-31 | 37309773 |
| 37309830 | Senior Software Developer (FinTech) 2 | 2022-05-31 | 37309773 |
| 37116394 | Senior .NET Developer (Windows Forms) | 2022-03-31 | 37116394 |
CodePudding user response：
We can use a regex substring to include the job name up to and including term in parentheses, to allow us to group together common jobs. Then use MIN() as an analytic function report the earliest job ID.
SELECT Job, Title, EndOfMonth,
MIN(Job) OVER (PARTITION BY REGEXP_SUBSTR(Title, '.*\\(.*?\\)')) AS RelatedJob
FROM yourTable;