Archives of the TeradataForum

Message Posted: Tue, 24 May 2005 @ 07:12:43 GMT


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

Subj:		Re: Group by on one column but select multiple columns

From:		Dieter Noeth

Anomy Anom wrote:

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.

You could rewrite it using a [correlated] subquery, but the optimizer will change it to a join again.

How can I do it efficiently. My other choice to avoid spool space was to create a volatile table instead of derived table.

A volatile table is materialized in spool :-)

     > 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;

Replace it with an OLAP function:

     select emp_name, emp_id, last_dt
     from
     employee K1
     qualify rank() over (partition by emp_id order by last_dt desc) = 1

For your case it's usually using less CPU, but it might not be helpful for your spool problem.

Another solutiuon is to beg your DBA for more spool :-)

Dieter


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

Attachments

Library

Quick Reference