How to compare columns of records from one table?

Question

Here are my test table data:

Testing

ID Name Payment_Date Fee Amt 1 BankA 2016-04-01 100 20000 2 BankB 2016-04-02 200 10000 3 BankA 2016-04-03 100 20000 4 BankB 2016-04-04 300 20000 

I am trying to compare the Name, Fee and Amt fields of each data record to see if the same values ​​exist or not. If they got the same value, I would like to mark something like "Y" for these entries. Here is the expected result

 ID Name Payment_Date Fee Amt SameDataExistYN 1 BankA 2016-04-01 100 20000 Y 2 BankB 2016-04-02 200 10000 N 3 BankA 2016-04-03 100 20000 Y 4 BankB 2016-04-04 300 20000 N 

I tried these two methods below. but I’m looking for any other solutions, so I can choose the best one for my work.

Method 1

 select t.*, iif((select count(*) from testing where name=t.name and fee=t.fee and amt=t.amt)=1,'N','Y') as SameDataExistYN from testing t 

Method 2

 select t.*, case when ((b.Name = t.Name) and (b.Fee = t.Fee) and (b.Amt = t.Amt)) then 'Y' else 'N' end as SameDataExistYN from testing t left join ( select Name, Fee, Amt from testing Group By Name, Fee, Amt Having count(*)>1 ) as b on b.Name = t.Name and b.Fee = t.Fee and b.Amt = t.Amt 

Answer 1

Here is another method, but I think you need to run tests on your data to find out which is better:

 SELECT t.*, CASE WHEN EXISTS( SELECT * FROM testing WHERE id <> t.id AND Name = t.Name AND Fee = t.Fee AND Amt = t.Amt ) THEN 'Y' ELSE 'N' END SameDataExistYN FROM testing t ; 

Answer 2

There are several approaches with differences in performance characteristics.

One option is to perform a correlated subquery. This approach is best if you have a suitable index and you occupy a relatively small number of rows.

 SELECT t.id , t.name , t.payment_date , t.fee , t.amt , ( SELECT 'Y' FROM testing s WHERE s.name = t.name AND s.fee = t.fee AND s.amt = t.amt AND s.id <> t.id LIMIT 1 ) AS SameDataExist FROM testing t WHERE ... LIMIT ... 

The correlated subquery in the SELECT list returns Y if at least one match string is found. If no match string is found, the SameDataExist column will be NULL. To convert NULL to "N", you can wrap the subquery in the IFULL () function.

---

Your method 2 is an acceptable approach. An expression in a SELECT list does not have to perform all these comparisons that have already been performed on join predicates. All you need to know is whether a matching row is found ... just check one of the columns for NULL / NOT NULL.

 SELECT t.id , t.name , t.payment_date , t.fee , t.amt , IF(s.name IS NOT NULL,'Y','N') AS SameDataExists FROM testing t LEFT JOIN ( -- tuples that occur in more than one row SELECT r.name, r.fee, r.amt FROM testing r GROUP BY r.name, r.fee, r.amt HAVING COUNT(1) > 1 ) s ON s.name = t.name AND s.fee = t.fee AND s.amt = t.amt WHERE ... 

---

You can also use EXISTS (correlated subquery)

Answer 3

check it

Select an operator to find duplicates in specific fields.

Not sure how to mark this as a hoax ...

Answer 4

Choose t.name, t.fee, t.amt if (count (*)> 1), 'Y', 'N') from testing the t-group for t.name, t.fee, t. amt