220
edits
Changes
Jump to navigation
Jump to search
→Cleaning the Companybase table
FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM companybase1 WHERE alwaysusflag = 1 AND undisclosedflag = 0)a;
--44770
By looking at the counts you can see that there is still 1 duplicate key in the table. Let's find it another way.Running the query below finds the key (coname, statecode, datefirstinv) that appears twice in the table. SELECT * FROM (SELECT coname, statecode, datefirstinv FROM companybase1 WHERE alwaysusflag = 1 AND undisclosedflag = 0)AS key GROUP BY coname, statecode, datefirstinv HAVING COUNT(key) > 1;The output looks like this: coname | statecode | datefirstinv ----------------------------+-----------+-------------- New York Digital Health LLC | NY | 2015-08-13
