Oracle aggregate functions on strings

Question

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?

How do you know which of the two values to pick? Is it equally correct to get "brad,pitt" and "[email protected]" as it is to get "mike,clark" and "[email protected]" so long as you don't get one value from each row? — Justin Cave
– Justin Cave, Commented Feb 26, 2015 at 20:35
Perhaps, the latest of the two will be ideal. Sorry for being vague. — Random_Questions
– Random_Questions, Commented Feb 26, 2015 at 21:06
Define "latest". Is there some date or timestamp column that can be used to identify the latest row? Is there a monotonically increasing key column that can be used? — Justin Cave
– Justin Cave, Commented Feb 26, 2015 at 21:23
There is a time stamp. So, doing a dense rank over them and ordering it by the time stamp gave me what I was looking for. Thanks for driving me home :D — Random_Questions
– Random_Questions, Commented Mar 5, 2015 at 14:38

Aramillo · Accepted Answer · 2015-02-26 20:35:03Z

1

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]

answered Feb 26, 2015 at 20:35

Aramillo

3,2263 gold badges27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Random_Questions Over a year ago

dense rank gave me what I was looking for. So, here is another question. If I have 20 fields that I want to fix. Will adding dense rank to all 20, create a performance issue? I am looking at >million rows.

Aramillo Over a year ago

Analytic functions add performance to queries, but i don't really understand your point, could you put this as other question, with more details and sample data?

xQbert · Accepted Answer · 2015-02-26 21:34:19Z

1

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

edited Feb 26, 2015 at 21:34

answered Feb 26, 2015 at 20:37

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Collectives™ on Stack Overflow

Oracle aggregate functions on strings

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related