CODEWITHSUNDEEP
sqloracle-databaseselect
Branderson20
Branderson20

Reputation: 326

DISTINCT for two out of three columns

I want the DISTINCT/FirstValue when a unique account_id value for one Person_id and one ah_person_id, doesn't matter which one, but also sometimes there Person_ID with a different Ah_person_id as well. Example Table

Person   AH_PERSON   ACCOUNT_ID
A         1           22
B         2           23
B         2           24 
C         3           25
C         4           26

What I want back Expected Results

Person   AH_PERSON   ACCOUNT_ID
A         1           22
B         2           23
C         3           25
C         4           26

I tried using Group by and DISTINCT but I don't get the results I want, Can anyone have an example? Thank You

SELECT DISTINCT person_id,
(SELECT ah_person_id
  FROM w_accnt
WHERE id = My_Table.account_id) AH_PERSON ,
account_id 
FROM My_Table

Upvotes: 1

Views: 80

Answers (3)

Alessandro Bardini
Alessandro Bardini

Reputation: 122

Could you please try this script? it can be the solution for you question

;WITH cte AS(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY AH_PERSON ORDER BY ACCOUNT_ID ASC) AS OrdererAccounts
from
#MyTable
)

SELECT
    Person, AH_PERSON, ACCOUNT_ID
FROM
    cte
WHERE
    OrdererAccounts = 1
ORDER BY
    PERSON

Upvotes: 1

Alessandro Bardini
Alessandro Bardini

Reputation: 122

You can use sql ROW_NUMBER function to solve that

Upvotes: 1

Mureinik
Mureinik

Reputation: 311348

I'd group by the person_id and ah_person_id, and take the minimal account_id:

SELECT   person_id, ah_person_id, MIN(account_id)
FROM     mytable
GROUP BY person_id, ah_person_id

Upvotes: 3

Related Questions