6 Replies - 566 Views - Last Post: 11 January 2017 - 10:14 AM

#1 dag72  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 205
  • Joined: 02-March 11

check for same value in a single column

Posted 11 January 2017 - 09:30 AM

Hello there,

I'm trying to avoid fuzzy matching to find duplicate values in a single column, because it is really an expensive process. I tried the following which works syntactically but doesn't produce the correct result.

case when sort_code = sort_code then 'same sort code' else '' end as sort_code


Would anyone know how I could accomplish this with?

Many thanks

This post has been edited by dag72: 11 January 2017 - 09:30 AM


Is This A Good Question/Topic? 0
  • +

Replies To: check for same value in a single column

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13563
  • View blog
  • Posts: 54,112
  • Joined: 12-June 08

Re: check for same value in a single column

Posted 11 January 2017 - 09:44 AM

You could always use a group by and count..
Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: check for same value in a single column

Posted 11 January 2017 - 09:49 AM

when sort_code = sort_code doesn't work because it is always true. It is comparing the same value, in the current row. (Except in the case of null, that is.)

Are you looking for "duplicate" values? That is, the exact same value(s), or do you require a fuzzy match? Looking for some text that is present in other text is the middle ground. Examples (of matches and non-matches) would help.
Was This Post Helpful? 0
  • +
  • -

#4 dag72  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 205
  • Joined: 02-March 11

Re: check for same value in a single column

Posted 11 January 2017 - 10:03 AM

Agree! Yes, I'm looking for duplicate values that is exactly the same. I believe that fuzzy matching would be ideal but it is very slow.
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13563
  • View blog
  • Posts: 54,112
  • Joined: 12-June 08

Re: check for same value in a single column

Posted 11 January 2017 - 10:07 AM

You are using the term 'fuzzy match' - what is your interpretation of that?
Was This Post Helpful? 0
  • +
  • -

#6 dag72  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 205
  • Joined: 02-March 11

Re: check for same value in a single column

Posted 11 January 2017 - 10:13 AM

View Postmodi123_1, on 11 January 2017 - 09:44 AM, said:

You could always use a group by and count..


I have used the group by and I know there are duplicates from this but I want to show these results line by line for each if that makes sense.

As follow

SELECT
      name,
      Payee_Name__C,
      sort_code,
      account_number__c,
      account_number__c,
      roll_code__c,
    COUNT(1) as CNT
FROM	xxx.account
where roll_code__c is null
GROUP BY	
        name,
        Payee_Name__C,
        sort_code,
        account_number__c,
        account_number__c,
        society_roll_code__c
HAVING COUNT(1) > 1;



View Postmodi123_1, on 11 January 2017 - 10:07 AM, said:

You are using the term 'fuzzy match' - what is your interpretation of that?

I'm using like so:

f_ratio(sort_code,sort_code)>89

Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13563
  • View blog
  • Posts: 54,112
  • Joined: 12-June 08

Re: check for same value in a single column

Posted 11 January 2017 - 10:14 AM

Then use a join against your main table with the group by query.. all on your keys.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1