-
query help
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
-
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
)
-
Is the emp_no unique in emp1 and emp2 tables?
Tamil
-
Tamil,
Emp_no is Unique in both the tables.
akkerend,
Do u think Using UNION instead of UNION ALL could give wrong result ???
-
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.
-
NO UNION - NO NOTHING.
PHP Code:
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
Last edited by tamilselvan; 09-22-2004 at 10:10 AM.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|