Recursive query with CTE

Question

I need some help with one query. So, I already have CTE with the next data:

ApplicationID	CandidateId	JobId	Row
1	1	1	1
2	1	2	2
3	1	3	3
4	2	1	1
5	2	2	2
6	2	5	3
7	3	2	1
8	3	6	2
9	3	3	3

I need to find one job per candidate in a way, that this job was distinct for table.

I expect that next data from query (for each candidate select the first available jobid that's not taken by the previous candidate):

ApplicationID	CandidateId	JobId	Row
1	1	1	1
5	2	2	2
8	3	6	2

I have never worked with recursive queries in CTE, having read about them, to be honest, I don't fully understand how this can be applied in my case. I ask for help in this regard.

Answer 1

The following query returns the expected result.

WITH CTE AS
(
  SELECT TOP 1 *,ROW_NUMBER() OVER(ORDER BY ApplicationID) N,
    CONVERT(varchar(max), CONCAT(',',JobId,',')) Jobs
    FROM ApplicationCandidateCTE
    ORDER BY ApplicationID
  UNION ALL
  SELECT a.*,ROW_NUMBER() OVER(ORDER BY a.ApplicationID),
    CONCAT(Jobs,a.JobId,',') Jobs
    FROM ApplicationCandidateCTE a JOIN CTE b
    ON a.ApplicationID > b.ApplicationID AND
       a.CandidateId > b.CandidateId AND
       CHARINDEX(CONCAT(',',a.JobId,','), b.Jobs)=0 AND
       b.N = 1
)
SELECT * FROM CTE WHERE N = 1;

However, I have the following concerns:

• The recursive CTE may extract too many rows.
• The concatenated JobId may exceed varchar(max).

See dbfiddle.