Recursive query with CTE-CodePudding

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.

CodePudding user response：

Does the query below give you the results you expect?

WITH CTE AS
(
  SELECT TOP 1 *,ROW_NUMBER() OVER(ORDER BY ApplicationID) N
    FROM ApplicationCandidateCTE
  UNION ALL
  SELECT a.*,ROW_NUMBER() OVER(ORDER BY a.ApplicationID)
    FROM ApplicationCandidateCTE a JOIN CTE b
    ON a.ApplicationID > b.ApplicationID AND
       a.CandidateId > b.CandidateId AND
       a.JobId <> b.JobId AND
       b.N = 1
)
SELECT * FROM CTE WHERE N = 1;