You are currently viewing the BOOK: Professional SQL Server 2000 Programming section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .

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.

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