CODEWITHSUNDEEP
oracle-databaseplsqlgrouping
Akram Shahda
Akram Shahda

Reputation: 14781

plsql: Getting another field values along with the aggregation values in a grouping statement

I am working on a time attendance system. I have the employees' transactions stored in the following table:

Transaction Table

I want to get the earliest and the latest transactions for each employee including their date and type.

I am able to get the dates using grouping and aggregation. However, I am not able to figure out how to get types with them.

Would you please help me in it.

Thank you.

Upvotes: 2

Views: 2068

Answers (2)

Rob van Wijk
Rob van Wijk

Reputation: 17705

That's what the FIRST and LAST aggregate functions are designed for.

Here is a link to the documentation:

FIRST: http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions065.htm#SQLRF00641 LAST: http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions083.htm#sthref1206

And here is an example:

SQL> create table my_transactions (id,employee_id,action_date,type)
  2  as
  3  select 1, 1, sysdate, 'A' from dual union all
  4  select 2, 1, sysdate-1, 'B' from dual union all
  5  select 3, 1, sysdate-2, 'C' from dual union all
  6  select 4, 1, sysdate-3, 'D' from dual union all
  7  select 5, 2, sysdate-11, 'E' from dual union all
  8  select 6, 2, sysdate-12, 'F' from dual union all
  9  select 7, 2, sysdate-13, 'G' from dual
 10  /

Table created.

SQL> select *
  2    from my_transactions
  3   order by id
  4  /

        ID EMPLOYEE_ID ACTION_DATE         T
---------- ----------- ------------------- -
         1           1 04-07-2011 10:15:07 A
         2           1 03-07-2011 10:15:07 B
         3           1 02-07-2011 10:15:07 C
         4           1 01-07-2011 10:15:07 D
         5           2 23-06-2011 10:15:07 E
         6           2 22-06-2011 10:15:07 F
         7           2 21-06-2011 10:15:07 G

7 rows selected.

SQL> select employee_id
  2       , min(action_date) min_date
  3       , max(type) keep (dense_rank first order by action_date) min_date_type
  4       , max(action_date) max_date
  5       , max(type) keep (dense_rank last order by action_date) max_date_type
  6    from my_transactions
  7   group by employee_id
  8  /

EMPLOYEE_ID MIN_DATE            M MAX_DATE            M
----------- ------------------- - ------------------- -
          1 01-07-2011 10:15:07 D 04-07-2011 10:15:07 A
          2 21-06-2011 10:15:07 G 23-06-2011 10:15:07 E

2 rows selected.

Regards,
Rob.

Upvotes: 6

Samuel
Samuel

Reputation: 2490

You could try to use analytical(or windowing functions)

select *
from 
(select id, employee_id, action_date,type,
       max(action_date) over (partition by employee_id) max_action_date,
       min(action_date) over (partition by employee_id) min_action_date
from transaction)
where action_date in (max_action_date, min_action_date)

Upvotes: 3

Related Questions