Avoid duplicates in SELECT

SELECT *
FROM FUND
INNER JOIN POSITION ON FUND.ACCT = POSITION.ACCT
INNER JOIN SECURITY ON POSITION.SEC_ID = SECURITY.SEC_ID
WHERE (FUND.MANAGER = 'smith')
I get duplicate SEC_ID's with this query because Smith may be the manager of
multiple funds that hold positions in the same security. For this query I
only need to return one record per security, doesn't matter which.
Any help would be appreciated.Ah, the old "any row will do" trick... why bother with the extra data if it
really doesn't matter which row it is?
Can you provide DDL, sample data, and desired results? Please see
http://www.aspfaq.com/5006 to help you give better specs and get better
answers
"Terri" <terri@.cybernets.com> wrote in message
news:dbmmpg$4rg$1@.reader2.nmix.net...
> SELECT *
> FROM FUND
> INNER JOIN POSITION ON FUND.ACCT = POSITION.ACCT
> INNER JOIN SECURITY ON POSITION.SEC_ID = SECURITY.SEC_ID
> WHERE (FUND.MANAGER = 'smith')
> I get duplicate SEC_ID's with this query because Smith may be the manager
> of
> multiple funds that hold positions in the same security. For this query I
> only need to return one record per security, doesn't matter which.
> Any help would be appreciated.
>|||At least I didn't ask for the "first" row :)
In this case it truly doesn't matter. The business problem is, Provide a
unique list of securities held by each manager. Managers don't hold
securities directly, they manage multiple funds which in turn hold
securities. Let me get together some DDL. Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Ht5xFYjFHA.232@.TK2MSFTNGP10.phx.gbl...
> Ah, the old "any row will do" trick... why bother with the extra data if
it
> really doesn't matter which row it is?
> Can you provide DDL, sample data, and desired results? Please see
> http://www.aspfaq.com/5006 to help you give better specs and get better
> answers

>|||> At least I didn't ask for the "first" row :)
Well, it's pretty much the same concept, and the same kind of problem that
SQL Server doesn't know how to solve.|||How does DISTINCT "know" which value to select from a list that contains
duplicates?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e4Vr8cgjFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Well, it's pretty much the same concept, and the same kind of problem that
> SQL Server doesn't know how to solve.
>|||>> How does DISTINCT "know" which value to select from a list that contains
duplicates? <<
A table is not a list. And the result set is a new table build from
the query. You are still thinking in physical terms, as if the rows
were punch cards.|||On Wed, 20 Jul 2005 17:12:47 -0600, Terri wrote:

>SELECT *
>FROM FUND
>INNER JOIN POSITION ON FUND.ACCT = POSITION.ACCT
>INNER JOIN SECURITY ON POSITION.SEC_ID = SECURITY.SEC_ID
>WHERE (FUND.MANAGER = 'smith')
>I get duplicate SEC_ID's with this query because Smith may be the manager o
f
>multiple funds that hold positions in the same security. For this query I
>only need to return one record per security, doesn't matter which.
>Any help would be appreciated.
>
Hi Terri,
After reading the thread, I'd say that half of your problem is solved if
you replace "SELECT *" with "SELECT column, column, ...", listing only
the columns you need. Probably "SELECT SecID" or something like that.
The other half of your problem is elementary once the irrelevant extra
columns are no longer in the select list: simply inset the keyword
DISTINCT between SELECT and the column list and the duplicates are gone.
SELECT DISTINCT SecID
FROM Funds
INNER JOIN Positions
ON Positions.Acct = Funds.Acct
INNER JOIN Securities
ON Securites.SecID = Positions.SecID
WHERE Funds.Manager = 'Smith'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)