CODEWITHSUNDEEP
oracle-database
Random_Questions
Random_Questions

Reputation: 3

Oracle aggregate functions on strings

I have an employee with multiple managers. The manager name field has (firstname,lastname) and the email field has([email protected]).There is no Mgr id. So, when I try to group this by employee id to get the max of Mgr name and email, some times I end up getting the wrong name/email id combination.

ex:

person  Mgr_name    Mgr_email
------- ---------   ----------
111     brad,pitt   [email protected]
111     mike,clark  [email protected]

when I group it by person and get the max(mgr_name),mgr_email, I get 

person  max(Mgr_name)    max(Mgr_email)
------- ---------        ----------
111     mike,clark        [email protected]

How do I get the correct email/name combination?

Upvotes: 0

Views: 140

Answers (2)

xQbert
xQbert

Reputation: 35323

You could use a subselect to obtain the max mgr_name for each person in the table then join it back to the base results to limit to only display each persons "Max" manager...

SELECT t1.Person, t1.Mgr_name, t1.mgr_email
FROM tableName t1
INNER JOIN (Select max(mgr_name) mname, Person from TableName group by person) t2
 on t1.mgr_name = t2.mname
 and t2.Person = T1.Person

Upvotes: 1

Aramillo
Aramillo

Reputation: 3216

Use row_number analytical function instead:

with t(person  ,Mgr_name ,   Mgr_email) as (
select 111     ,'brad,pitt'  , '[email protected]' from dual union all
select 111     ,'mike,clark' , '[email protected]' from dual )

select person  ,Mgr_name ,   Mgr_email from (
select t1.*, row_number() over (order by mgr_name) num from t t1)
where num = 1

This get max mgr_name with correct email.

Output:

    PERSON MGR_NAME   MGR_EMAIL          
---------- ---------- -------------------
       111 brad,pitt  [email protected]

Upvotes: 1

Related Questions