Hi, I got a table (BANK) that have a primary key composed by three columns (id_organization, sco_id_person and sco_or_account) I want a result set that brings me just one register by person (sco_id_person). The problem is that some persons have more than one organization and when I do the join I get some sco_id_person repeats.


The sql i did is:

SELECT
UNIQUE(A.SCO_ID_PERSON),
A.SCO_ID_BANK_BRANCH,
A.SCO_ACCOUNT_NUMBER
FROM
BANK A
, (SELECT
SCO_ID_PERSON, MAX(SCO_OR_ACCOUNT) AS SCO_OR_ACCOUNT
FROM
BANK
GROUP BY SCO_ID_PERSON
) B
WHERE
A.SCO_ID_PERSON = B.SCO_ID_PERSON
AND A.SCO_OR_ACCOUNT = B.SCO_OR_ACCOUNT

p.s: sco_or_account is a field that contains a number, and each time a register is updated, a new row is inserted with a higher sco_or_account, so a history is kept and for my query i need the last rows (max())

you didn't say what the problem is

i'm going to guess that it's the UNIQUE() function, which i've never heard of

The problem is that I got rows with the same sco_id_person and that is because they belong to other organization (id_organization).

I want all the persons but just once.

Thanks

okay, yes, sorry, i understand now (should have read your question more closely)

what else besides the MAX(SCO_OR_ACCOUNT) do you need to see for each person?

I need to see in the result set the sco_id_person, sco_bank_branch and sco_account number but just one line per person (sco_id_person) because the problem is that as I told you that are many rows for each person because the belong to differents organizations (id_organization) and everytime the information is updated a new line is inserted (sco_or_account plus 1)

Many thanks

I need to see in the result set the sco_id_person, sco_bank_branch and sco_account number but just one line per person (sco_id_person) because the problem is that as I told you that are many rows for each person because the belong to differents organizations (id_organization) and everytime the information is updated a new line is inserted (sco_or_account plus 1)

Many thanks

okay, i understand the "one line per person" part

now i ask you: if the person belongs to different organizations, which sco_bank_branch and sco_account number do you want to see?

or are the sco_bank_branch and sco_account number the same for all organizations that a person belongs to?

Yes, you are right, sorry I didn`t mention it. Both sco_account_number and sco_bank_branch have differents values for the same person, in fact I do not care wich one to take, just one.

Many thanks for your time!