I have been taking from this board for awhile now and thought I would try to give back. I am sure this is elementary to many of you that are well versed in SQL but I created a query yesterday that solved a big problem for me and I wanted to share it.
Now the issue was that I had a table with the columns: phone number, installed, disconnected. The phone numbers were listed multiple times mostly with null values but sometimes with legit data.
IE:
telnumber | Installed | Disconnected
555-1212 12-12-2005 05-06-2007
555-1212 NULL NULL
666-1212 NULL NULL
666-1212 NULL NULL
My goal was to eliminate duplicates from the table, keeping the phone number with legit data over NULL, but if the phone number was listed twice both times with NULL fields, then just keep one of them...
EXAMPLE CODE
Code:
SELECT DISTINCT * INTO temp_table
FROM original_table
WHERE column1 <> '' OR column2 <> ''
ORDER BY column1 ASC
SELECT * INTO FINAL_table
FROM temp_table
UNION
SELECT DISTINCT * FROM original_table
WHERE (column1 NOT IN
(SELECT column1 FROM temp_table)
)
SELECT * FROM FINAL_table
ACTUAL CODE
Code:
SELECT DISTINCT * INTO phone_temp
FROM phone_original
WHERE installed <> '' OR disconnected <> ''
ORDER BY telnumber ASC
SELECT * INTO phone_FINAL
FROM phone_temp
UNION
SELECT DISTINCT * FROM phone_original
WHERE (telnumber NOT IN
(SELECT telnumber FROM phone_temp)
)
SELECT * FROM phone_FINAL
wrote remove duplicate query
