220
edits
Changes
Jump to navigation
Jump to search
→Removing duplicates from ipos table
GROUP BY issuer, issuedate, statecode;
--9491
To clean these up I built the following tables and selected the max principalamt values if there were duplicates. There is probably a simpler way to do this but I'll leave that for the next person to discover.
DROP TABLE iposcounthigh;
CREATE TABLE iposcounthigh AS
SELECT *
FROM iposcount
WHERE count > 1;
DROP TABLE duplicateipodata;
CREATE TABLE duplicateipodata AS
SELECT ipos.*
FROM ipos INNER JOIN iposcounthigh ON (ipos.issuer = iposcounthigh.issuer AND ipos.issuedate = iposcounthigh.issuedate AND
ipos.statecode = iposcounthigh.statecode)
ORDER BY principalamt DESC;
DROP TABLE ipoduplicatemin;
CREATE TABLE ipoduplicatemin AS
SELECT issuedate, issuer, statecode, MIN(principalamt) AS principalamt
FROM duplicateipodata
GROUP BY issuedate, issuer, statecode;
--939
\COPY ipoduplicatemin TO 'ipoduplicatemin.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
DROP TABLE ipoduplicatekeys;
CREATE TABLE ipoduplicatekeys AS
SELECT *,
concat(issuer::text, issuedate::text, statecode::text, principalamt::text) AS pkey
FROM ipoduplicatemin;
DROP TABLE iponoduplicates;
CREATE TABLE iponoduplicates AS
SELECT ipospkey.*
FROM ipospkey
WHERE NOT EXISTS(SELECT * FROM ipoduplicatekeys WHERE (ipospkey.pkey = ipoduplicatekeys.pkey));
--9490
