SELECT DISTINCT syntax question 5

[tahoskier]

I have a address table with multiple family members in it. I am using the SELECT DISTINCT command to remove the extra family members for a mailer. The DISTINCT FIELDS are address, city, state, zip and lname. How can I export into a new table a first name also? I am not picky about which name.

Here is the exact statement that I am using.

SELECT DISTINCT address, city, state, zip, lname INTO TableNoDups from Table1

 

[ClaireHCM]

Does your query succeed?all you need is how to insert the result into a new table?

(1)create a table(say table2) with
address, city, state, zip, lname fileds.

(2)execute the following query

insert into table2 select DISTINCT address, city, state, zip, lname from table1

 

[tahoskier]

But How do insert a fname field which is a non DISTINCT field?

Thanks for your quick reply.

 

[ClaireHCM]

I am confused now,you need all the other column's value to be distinct but not for fname.Then whatever you did for distinct will be destroyed by fname.(basically if you mix non distinct and distinct = non distinct!)

Then how the new table will look like.Can you give the result of table how you want it to be?

 

[sunila7]

Hi,

Try this query.. Only one first name is returned for a lastname....

SELECT DISTINCT address, city, state, zip, lname ,
(SELECT Top 1 FName from Table1 T Where T.Lname = T1.Lname) FNAme
INTO TableNoDups from Table1 T1

But if 2 families have same lastname this query will fail
probably u can use address,city,state,zip also reduce chances of duplication in result. Do u have some kind of ID field in the DB for each family? If so, u can use that instead of Lname.

Hope it helps

Sunil

 

[tahoskier]

Thanks for everyones advice. I think I will modify the tables.

 

[JamesLean]

If you just want to take any one firstname for each family then this will do it:

SELECT address, city, state, zip, lname, MIN(fname) AS fname
  INTO tablenodups
FROM table1
GROUP BY address, city, state, zip, lname

--James

 

[tlbroadbent]

Good job, James. Sometimes the simple solution escapes us.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[tahoskier]

Thanks for all the responses.