How can I display the first record of duplicate record of one field?

[Hiroko]

Hi All,

Can anybody help me?

There is a table with ContactPersonsName, CompanyName, and
CompanyAddress.
There are multiple entries with the same company name if there are
multiple ContactPersonsName.

I'd like to select only one row (the first row) for a company so that I
don't mail multiple letters to a company.

How can I do this???

SELECT DISTINCT ContactPersonsName, CompanyName, CompanyAddress
FROM table

doesn't work because I need ContactPersonsName is all different.

I need to display all fields in the table to create a mailing label..

Thanks for your help

Hiroko

--
Hiroko Ohashi
Department Coordinator
Programming Department
Elucidex
655 Stuart Road
Bellingham, WA 98226

***********

Tel: (360)647-1228（英語）
Tel: (360)734-6356 (日本語)
Tel: 1-800-755-9931(Toll Free)
Fax: (360)714-8927
Hoh...@elucidex.com
http://www.elucidex.com
http://www.elucidex.com/jpn

[Alexander Tarasul]

the solution is:

select companyname,min(contactpersonname),min(companyaddress)
from companylist

but you would be much better having normalized database.

------------------------------------------------
Alexander Tarasul
Microsoft Certified Solution Developer(SQL Server,VB)
Certified Borland Delphi Client Server Developer
shl...@ripco.com
http://pages.ripco.com/~shleym
SQL Answers located at
http://pages.ripco.com/~shleym/sql_answers.htm

---------------
Hiroko wrote in message <372A0FA7...@elucidex.com>...

[Richard Romley]

This will do what you want:

select c.ContactPersonsName, c.CompanyName, c.CompanyAddress

from (select CompanyName, min(ContactPersonsName)
from CompanyTable
group by CompanyName
)as x(CompanyName, FirstContactName)

join CompanyTable as c
on x.CompanyName = c.CompanyName
and x.FirstContactName = c.ContactPersonsName

Richard

**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****

[Roy Harvey]

Alexander,

>the solution is:
>
>select companyname,min(contactpersonname),min(companyaddress)
>from companylist

The problem with this is that the contactpersonname and companyaddress
could have come from different rows, and of course there is no
guarantee that they are the same.

>but you would be much better having normalized database.

Amen!

Roy

[Roy Harvey]

Hiroko,

If Alexander's answer isn't sufficient you could try something like:

SELECT ContactPersonsName, CompanyName, CompanyAddress
FROM tbl as T1
WHERE ContactPersonsName = (select min(T2.ContactPersonsName)
from tbl as T2
where T1.CompanyName = T2.Company_name)

Roy

[Hiroko]

Hi Alexander, Richard, and Roy,

Thank you for your reaponse.

This was the first time I wrote to this NewsGroup.
And I was very amaized that how quickly I received responses.
(Actually, I thought it might take a few days to get any responses, so
I went home right after I sent the mail.
Today, I opened up the NewsGroup, and WoW there are answers!!)

Thank you very much.

Hiroko.

Alexander Tarasul wrote:
>
> agree thank you
>
> Roy Harvey <76300...@compuserve.com> wrote in message
> <372b25c0....@msnews.microsoft.com>...
> >Alexander,

[Hiroko]

Richard,

IT WORKED!
Thank you very much.

Now only one thing it didn't work was Null Value.
Because Min() function skips null value, the list didn't pick up any
companies which didn't have ContactPersonsName.

I decided to run another SQL Query for Null values for now.

Thanks again.

Hiroko

[Hiroko]

Roy,

This Worked as Well as Richard's one!
(This didn't pick up NULL values as well as Richard's one since it is
done by Min().)

Thanks very much!!

Hiroko