DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: query help

  1. #1
    Join Date
    Sep 2002
    Posts
    376

    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

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    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
    )

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Is the emp_no unique in emp1 and emp2 tables?

    Tamil

  4. #4
    Join Date
    Sep 2002
    Posts
    376
    Tamil,
    Emp_no is Unique in both the tables.

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

  5. #5
    Join Date
    Jul 2000
    Posts
    296
    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.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    NO UNION - NO NOTHING.

    PHP Code:
    Option In both tables all emp_no are found.
                 
    In this case, use equijoin.

    08:
    05:44 SQLselect 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 SQLselect 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 SQLselect a.emp_nogreatest(a.lutb.lut)
    08:
    07:26   2  from emp1 aemp2 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 
    EMP2 table has less emp_no.
                 
    Thenyou need to outer join.

    08:
    07:40 SQLdelete emp2 where emp_no 20 ;

    1 row deleted.

    08:08:
    43 SQLcommit;

    Commit complete.

      
    1  select a.emp_nogreatest(a.lutnvl(b.lut,to_date('01-01-1800','DD-MM-YYYY') ))
      
    2   from emp1 aemp2 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 SQLselect from emp1;

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

    09:08:11 SQLselect from emp2 ;

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

    09:03:33 SQLget emp.sql
      1  select nvl
    (a.emp_nob.emp_no),
      
    2         greatestnvl(a.lutto_Date('01/01/1800','dd/mm/yyyy')) ,
      
    3                   nvlb.lutto_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_NOGREATEST(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.

  7. #7
    Join Date
    Sep 2002
    Posts
    376
    Thx Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width