First, you need to have some kind of ID on the records in the table, otherwise each record will be always matched to itself.
Assuming, you have such ID here is a test table (which should be really provided by you in the initial post):
--DROP TABLE #Student
SELECT IDENTITY(int, 1,1) RowID, *
INTO #Student
FROM (
SELECT 'Alison' as Student, NULL as IDNo, '1111' as Tel3, '4444' as Tel1, NULL as Sort UNION ALL
SELECT 'Alison' as Student, '80405088' as IDNo, '1111' as Tel3, '4444' as Tel1, NULL as Sort UNION ALL
SELECT 'Amber' as Student, NULL as IDNo, '5555' as Tel3, '6666' as Tel1, NULL as Sort UNION ALL
SELECT 'Amber' as Student, NULL as IDNo, '7777' as Tel3, '7777' as Tel1, NULL as Sort UNION ALL
SELECT 'Carmen' as Student, NULL as IDNo, '8888' as Tel3, '8888' as Tel1, NULL as Sort UNION ALL
SELECT 'Carmen' as Student, '20083085' as IDNo, '9999' as Tel3, '8888' as Tel1, NULL as Sort
) DT (Student, IDNo, Tel3, Tel1, Sort)
And here is the query returning matching pairs with appropriate Sort value:
SELECT T1.Student, T1.RowID, T1.IDNo, T1.Tel3, T1.Tel1,
CASE
WHEN T2.Tel3 = T1.Tel3 THEN 1111
WHEN T2.Tel1 = T1.Tel1 THEN 8888
WHEN T2.Tel1 = T1.Tel3 OR T2.Tel3 = T1.Tel1 THEN 7777
END Sort
, T2.RowID,T2.IDNo, T2.Tel3, T2.Tel1
FROM #Student T1
INNER JOIN #Student T2 ON T2.Student = T1.Student AND T2.RowID <> T1.RowID AND T2.IDNo IS NULL AND (
T2.Tel3 = T1.Tel3
OR T2.Tel1 = T1.Tel1
OR T2.Tel1 = T1.Tel3 OR T2.Tel3 = T1.Tel1
)
Assuming that it's the lines with IDNo which must be marked as duplicates, I apply the Sort status to T2 entries:
UPDATE T2
SET Sort = CASE
WHEN T2.Tel3 = T1.Tel3 THEN 1111
WHEN T2.Tel1 = T1.Tel1 THEN 8888
WHEN T2.Tel1 = T1.Tel3 OR T2.Tel3 = T1.Tel1 THEN 7777
END
FROM ..
Is it close to what you need?
_____________
Code for TallyGenerator