Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 23 May 2005 @ 19:55:20 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Group by on one column but select multiple columns
 
From:   Anomy Anom

<-- Anonymously Posted: Monday, May 23, 2005 15:22 -->

How do I write the following query with out a derived table. I want to get the maximum last_dt for a corresponding emp_name. Then I also want to display emp_id for a emp_name and last_dt. I am doing it like this. But becaz of the table size, I am getting spool space error frequently. How can I do it efficiently. My other choice to avoid spool space was to create a volatile table instead of derived table.

     select emp_name, emp_id, last_dt
     from
     employee K1,
     (
     select emp_id, max(last_dt)
     from employee
     group by 1
     ) K2(emp_id, max_last_dt)
     where
     K1.emp_id = K2.emp_id
     and
     K1.last_dt = K2.last_dt;

Thanks



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016