Distinct-Left join Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Distinct-Left join Question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-07 : 07:41:09

Harald writes "Hi

I am having a brain fart on figuring out the following problem. Let me illustrate what I have and what I want.

I have a two tables with the following records:

tblDoc
DocID DocClass
===== ========
123 Office
243 Developer
543 Developer

tblAdress
DocID ZValue
===== =====
243 Adr1
543 Adr2
243 Adr3
243 Adr4
543 Adr5

If I use following statement:

select tblAdress.ZValue, tblDoc.DocID, tblDoc.DocClass
from tblDoc left join tblAdress
on tbldoc.DocID = tblAdress.DocID
where tblDoc.DocClass = 'Developer'

I get following results:

ZValue DocID DocClass
====== ===== =========
Adr1 243 Developer
Adr3 243 Developer
Adr4 243 Developer
Adr2 543 Developer
Adr5 543 Developer

What I want to do is display only the first record of a DocID. So using the above as an example, the output would be:

ZValue DocID DocClass
====== ===== =========
Adr1 243 Developer
Adr2 543 Developer

Any ideas or thoughts? Cheers!
Harald"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-07 : 07:45:41

By first I assume you mean alphabetically.

select DocID, ZValue = min(ZValue)
from tblDoc left join tblAdress
on tbldoc.DocID = tblAdress.DocID
where tblDoc.DocClass = 'Developer'
group by DocID

This will give nulls if no tblAdress entry - if you only want those with an address make it an inner join.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources