Archives of the TeradataForum

Message Posted: Tue, 22 Feb 2005 @ 15:02:06 GMT

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

MDIFF has already been mentioned as one of the OLAP functions to do this. I believe that I would probably use RANK to find the first:

QUALIFY RANK() OVER (ORDER BY tran_id ASC, trans_date, trans_time) <2

If you need the first one on each day:

QUALIFY RANK() OVER (PARTITION ORDER BY trans_date ORDER BY tran_id ASC, trans_date, trans_time) <2

If you need the first by year and month:

QUALIFY RANK() OVER (PARTITION ORDER BY trans_date/100 ORDER BY tran_id ASC, trans_date, trans_time) <2

Of course, if you want more than one row you change the comparison value in the QUALIFY.

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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