By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
Id INTEGER NOT NULL PRIMARY KEY
,F1 INTEGER NOT NULL
,F2 INTEGER NOT NULL
,F3 INTEGER NOT NULL
,F4 INTEGER NOT NULL
,F5 INTEGER NOT NULL
,F6 INTEGER NOT NULL
);
INSERT INTO mytable(Id,F1,F2,F3,F4,F5,F6) VALUES (1,5,11,15,21,30,36);
INSERT INTO mytable(Id,F1,F2,F3,F4,F5,F6) VALUES (2,8,10,21,25,32,39);
INSERT INTO mytable(Id,F1,F2,F3,F4,F5,F6) VALUES (3,10,18,23,27,28,30);
3 rows affected
select t.*, (case when c.num_duplicates > 0 then 'Yes' else 'No' end) as lastDigit,
coalesce(c.num_duplicates, 0)
from mytable t cross apply
(select sum(cnt) as num_duplicates
from (select f % 10 as digit, count(*) as cnt
from (values (f1), (f2), (f3), (f4), (f5), (F6)) v(f)
group by f % 10
having count(*) > 1
) c
) c
| Id | F1 | F2 | F3 | F4 | F5 | F6 | lastDigit | (No column name) |
|---|---|---|---|---|---|---|---|---|
| 1 | 5 | 11 | 15 | 21 | 30 | 36 | Yes | 4 |
| 2 | 8 | 10 | 21 | 25 | 32 | 39 | No | 0 |
| 3 | 10 | 18 | 23 | 27 | 28 | 30 | Yes | 4 |