CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
user3352171
user3352171

Reputation: 53

Select one email address per user

I would like to select one email address per user based on the following rule. If preferred_email is Y, select that email address ([email protected]). If preferred_email is not Y, use that email address. Some users may only have a preferred_email value of N. I have tried using a case statement, but it didn't return the correct result.

This is an example of the table.

user_id email_address       preferred_email
25      [email protected]    N
25      [email protected]    Y
26      [email protected]    N
27      [email protected]    N

Upvotes: 0

Views: 94

Answers (2)

user5683823
user5683823

Reputation:

In Oracle 12.1 and higher, this can be done easily with the match_recognize clause, like this:

select user_id, email_address
from   inputs
match_recognize (
  partition by user_id
  order by preferred_email desc nulls last
  all rows per match
  pattern ( ^x )
  define x as 0 = 0
)
;

However, this solution (as well as some of the others proposed here) has a potential weakness: it relies on the explicit ordering of 'Y' vs 'N', and it assumes these are the only values possible in the preferred_email column (and also that the column is not nullable).

It would be better, if the column preferred_email is not constrained to non nullable and only possible values 'Y' and 'N', to have an order clause like

 order by case preferred_email when 'Y' then 0 end [...]

Unfortunately, the match_recognize clause can only order by columns, not by expressions. (Hoping this will be addressed in the future!) In that case, an aggregate solution using the FIRST/LAST aggregate function, as in MT0's answer, is the best choice - but with the ORDER BY clause changed accordingly.

select   user_id, 
         max(email_address) keep (dense_rank first 
                 order by case preferred_email when 'Y' then 0 end) as email_address
from     inputs
group by user_id
;

Upvotes: 0

MT0
MT0

Reputation: 168406

SELECT user_id,
       MAX( email_address ) KEEP ( DENSE_RANK FIRST ORDER BY preferred_email DESC, ROWNUM )
         AS email_address
FROM   your_table
GROUP BY user_id

or

SELECT user_id,
       email_address
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY user_id
                             ORDER BY preferred_email DESC, ROWNUM )
           AS rn
  FROM   your_table t
)
WHERE  rn = 1;

Upvotes: 2

Related Questions