CODEWITHSUNDEEP
sqldatabaseoracle-database
user3082877
user3082877

Reputation: 143

Concatenate columns and group them by the result of the concatenation

I have a table showing employees with their departments and their positions and the last_update of their positions. My objective is to list the latest position of each one in each departement (MAX last_update by department) for each employee. But the problem is that i can't concatenate two columns and group them after through this concatenation.

So for example the table is

Departement    Position      employee       last_update    
dep1              pos1       employee1          16:00
dep1              pos2       employee1          08:00
dep1              pos3       employee1          11:00   
dep2              pos4       employee2          13:00
dep2              pos5       employee2          09:00
dep2              pos6       employee3          07:00

The desired result should be:

Departement    Position      employee       last_update    
dep1              pos1       employee1          16:00
dep2              pos4       employee2          13:00
dep2              pos6       employee3          07:00

i have to concatenate the employee and his department and group them by their department and the name of the employee to get the result. But i can't have a concat into a group by like this with oracle: 

SELECT t.department, t.position,concat(t.department,t.employee), t.employee , r.MaxTime
FROM (SELECT department,position,employee, MAX(last_update) as MaxTime
  FROM employeetable
  GROUP BY (concat(department,employee))) r
  INNER JOIN employeetable t ON t.departement = r.department AND t.last_update = r.MaxTime

Thanks a lot

Upvotes: 0

Views: 380

Answers (4)

Art
Art

Reputation: 5782

Keeping it simple:

SELECT department, position, employee, last_update 
  FROM 
  (
   SELECT department, position, employee, last_update
   , ROW_NUMBER() OVER (PARTITION BY employee, department ORDER BY department, employee, last_update DESC) rseq
     FROM
  (
   select 'dep1' department, 'pos1' position, 'employee1' employee, to_char(to_date('19/02/2015 16:00', 'dd/mm/yyyy hh24:mi:ss'), 'mm-dd-yyyy hh24:mi:ss') last_update from dual union all
   select 'dep1' department, 'pos2' position, 'employee1' employee, to_char(to_date('19/02/2015 08:00', 'dd/mm/yyyy hh24:mi:ss'), 'mm-dd-yyyy hh24:mi:ss') last_update from dual union all
   select 'dep1' department, 'pos3' position, 'employee1' employee, to_char(to_date('19/02/2015 11:00', 'dd/mm/yyyy hh24:mi:ss'), 'mm-dd-yyyy hh24:mi:ss') last_update from dual union all
   select 'dep2' department, 'pos4' position, 'employee2' employee, to_char(to_date('19/02/2015 13:00', 'dd/mm/yyyy hh24:mi:ss'), 'mm-dd-yyyy hh24:mi:ss') last_update from dual union all
   select 'dep2' department, 'pos5' position, 'employee2' employee, to_char(to_date('19/02/2015 09:00', 'dd/mm/yyyy hh24:mi:ss'), 'mm-dd-yyyy hh24:mi:ss') last_update from dual union all
   select 'dep2' department, 'pos6' position, 'employee3' employee, to_char(to_date('19/02/2015 07:00', 'dd/mm/yyyy hh24:mi:ss'), 'mm-dd-yyyy hh24:mi:ss') last_update from dual
   )
 )
WHERE rseq = 1
/

dep1    pos1    employee1   02-19-2015 16:00:00
dep2    pos4    employee2   02-19-2015 13:00:00
dep2    pos6    employee3   02-19-2015 07:00:00

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191265

You can group by the separate department and employee columns in the subquery, and you don't want to include the position in that either; then in the outer query join on both columns:

SELECT t.department, t.position, t.employee,
  to_char(r.MaxTime, 'HH24:MI') as MaxTime
FROM (SELECT department, employee, MAX(last_update) as MaxTime
      FROM employeetable
      GROUP BY department, employee) r
INNER JOIN employeetable t ON t.department = r.department
  AND t.employee = r.employee
  AND t.last_update = r.MaxTime;

DEPARTMENT POSITION EMPLOYEE   MAXTIME
---------- -------- ---------- -------
dep1       pos1     employee1  16:00   
dep2       pos4     employee2  13:00   
dep2       pos6     employee3  07:00

You could also use an analytic ranking function in the subquery to avoid a self join:

SELECT department, position, employee,
  to_char(last_update, 'HH24:MI') as maxtime
FROM (
  SELECT department, position, employee, last_update,
    rank() over (partition by department, employee
      order by last_update desc) as rnk
  FROM employeetable
)
WHERE rnk = 1;

Or using max with keep first dense_rank:

SELECT department,
  max(position) keep (dense_rank first order by last_update desc) as position,
  employee,
  to_char(max(last_update)
    keep (dense_rank first order by last_update desc), 'HH24:MI') as maxtime
FROM employeetable
GROUP BY department, employee;

I'm assuming your last_update column is really a date, and you're only showing the time for brevity; otherwise you'd need to convert the value, and you'd have no way to rank times on different days...

SQL Fiddle with all three approaches.

Upvotes: 1

Njal
Njal

Reputation: 577

How about:

SELECT t.department, t.position, t.employee , t.last_update
From employeetable t
Where not exists (
  SELECT 'x'
  From employeetable t2
  Where t2.employee=t.employee
    and t2.last_update > t.last_update
  )

Upvotes: 0

Boneist
Boneist

Reputation: 23578

Why are you trying to group by the concatenation of two columns and not just the two columns themselves? Also, what is the datatype of the last_update column? I sincerely hope that it's either a DATE or a TIMESTAMP. Having said that, what is it you're trying to do - find the latest row for each department and employee? If so, then something like:

with sample_data as (select 'dep1' department, 'pos1' position, 'employee1' employee, to_date('19/02/2015 16:00', 'dd/mm/yyyy hh24:mi:ss') last_update from dual union all
                     select 'dep1' department, 'pos2' position, 'employee1' employee, to_date('19/02/2015 08:00', 'dd/mm/yyyy hh24:mi:ss') last_update from dual union all
                     select 'dep1' department, 'pos3' position, 'employee1' employee, to_date('19/02/2015 11:00', 'dd/mm/yyyy hh24:mi:ss') last_update from dual union all
                     select 'dep2' department, 'pos4' position, 'employee2' employee, to_date('19/02/2015 13:00', 'dd/mm/yyyy hh24:mi:ss') last_update from dual union all
                     select 'dep2' department, 'pos5' position, 'employee2' employee, to_date('19/02/2015 09:00', 'dd/mm/yyyy hh24:mi:ss') last_update from dual union all
                     select 'dep2' department, 'pos6' position, 'employee3' employee, to_date('19/02/2015 07:00', 'dd/mm/yyyy hh24:mi:ss') last_update from dual)
select department, position, employee, last_update
from   (select sd.*,
               row_number() over (partition by department, employee order by last_update desc) rn
        from   sample_data sd)
where  rn = 1;

DEPARTMENT POSITION EMPLOYEE  LAST_UPDATE          
---------- -------- --------- ---------------------
dep1       pos1     employee1 19/02/2015 16:00:00  
dep2       pos4     employee2 19/02/2015 13:00:00  
dep2       pos6     employee3 19/02/2015 07:00:00

Upvotes: 1

Related Questions