This should be simple but my brains not functioning. So I would appreciate some help from the SQL masters...
I have a table with first_name and a last_name column. I would like to find similar duplicates by finding the same last_name and matching the first two characters of the first name. Therefore if the table has the following rows: Ind_id last_name first_name 100 Smith david 101 Smith dave 102 Smith irene I would like it to pick out the top two rows. I'm using the following but while the syntax is correct the last where expression below is wrong: select * from current as x, current as y where x.last_name = y.last_name and x.ind_id != y.ind_id and x.first_name like substr(y.first_name, 0,2) In my english I was trying to write "match where the first name of x begins with the first two characters of y first name". Using a literal example for above the SQL would end: where x.first_name like 'da%' (Note: the ind_id comparison avoids matching itself) Thanks Nick ******************************************************************** This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ******************************************************************** _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users