query help

[bang_dba]

Hi,
I am using the following query to retrive the maximum date from two tables(emp1 and emp2) againest a emp_no.

select emp_no,max(lut) from (
select emp_no,lut from emp1
union
select emp_no,lut from emp2
)
group by emp_no


Is there any better way of writing this query.
I am exploring the possibility of eliminating the UNION

[akkerend]

1. Use union all:

select emp_no,max(lut) from (
select emp_no,lut from emp1
union all
select emp_no,lut from emp2
)
group by emp_no;

2. Use max in the inline view:

select emp_no,max(lut) from (
select emp_no,max(lut) lut from emp1
group by emp_no
union all
select emp_no,max(lut) lut from emp2
group by emp_no
)
group by emp_no;

3. Use a join:

select emp_no,greatest(max1,max2)
from (
select e1.emp_no, max(e1.lut) max1, max(e2.lut) max2
from emp1 e1 join emp2 e2 on (e1.emp_no = e2.emp_no)
group by e1.emp_no
)

[tamilselvan]

Is the emp_no unique in emp1 and emp2 tables?

Tamil

[bang_dba]

Tamil,
Emp_no is Unique in both the tables.

akkerend,
Do u think Using UNION instead of UNION ALL could give wrong result ???

[akkerend]

Why should union all give wrong result? Union removes duplicate rows, so the maximum value stays the same.

If emp_no is unique use union all.

[tamilselvan]

NO UNION - NO NOTHING.

Option - 1 : In both tables all emp_no are found.
             In this case, use equijoin.

08:05:44 SQL> select * from emp1;

    EMP_NO LUT
---------- --------------------
        10 12-SEP-2004 08:04:02
        20 12-SEP-2004 08:04:06
        30 02-SEP-2004 08:04:25

08:05:50 SQL> select * from emp2 ;

    EMP_NO LUT
---------- --------------------
        10 09-AUG-2004 08:05:21
        20 30-AUG-2004 08:05:32
        30 10-SEP-2004 08:05:42

08:05:56 SQL> select a.emp_no, greatest(a.lut, b.lut)
08:07:26   2  from emp1 a, emp2 b
08:07:32   3  where a.emp_no = b.emp_no ;

    EMP_NO GREATEST(A.LUT,B.LUT
---------- --------------------
        10 12-SEP-2004 08:04:02
        20 12-SEP-2004 08:04:06
        30 10-SEP-2004 08:05:42

OPTION - 2 : EMP2 table has less emp_no.
             Then, you need to outer join.

08:07:40 SQL> delete emp2 where emp_no = 20 ;

1 row deleted.

08:08:43 SQL> commit;

Commit complete.

  1  select a.emp_no, greatest(a.lut, nvl(b.lut,to_date('01-01-1800','DD-MM-YYYY') ))
  2   from emp1 a, emp2 b
  3* where a.emp_no = b.emp_no(+)
08:13:20 SQL> /

    EMP_NO GREATEST(A.LUT,NVL(B
---------- --------------------
        10 12-SEP-2004 08:04:02
        20 12-SEP-2004 08:04:06
        30 10-SEP-2004 08:05:42

Option - 3 full outer join 

09:03:40 SQL> select * from emp1;

    EMP_NO LUT
---------- -------------------
        10 12/09/2004 08:04:02
        20 12/09/2004 08:04:06

09:08:11 SQL> select * from emp2 ;

    EMP_NO LUT
---------- -------------------
        10 09/08/2004 08:05:21
        30 10/09/2004 08:05:42

09:03:33 SQL> get emp.sql
  1  select nvl(a.emp_no, b.emp_no),
  2         greatest( nvl(a.lut, to_Date('01/01/1800','dd/mm/yyyy')) ,
  3                   nvl( b.lut, to_date('01/01/1800','dd/mm/yyyy'))
  4                 )
  5  from emp1 a full outer join emp2 b
  6* on a.emp_no = b.emp_no
09:03:38 SQL> /

NVL(A.EMP_NO,B.EMP_NO) GREATEST(NVL(A.LUT,
---------------------- -------------------
                    10 12/09/2004 08:04:02
                    20 12/09/2004 08:04:06
                    30 10/09/2004 08:05:42 

I would suggest to use FULL OUTER JOIN method, if both tables have diff number of rows.

Tamil

[bang_dba]

Thx Tamil