Archives of the TeradataForum

Message Posted: Tue, 07 Jun 2005 @ 13:59:33 GMT

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

All

I have been asked to forward this on to the list for suggestions.

From my colleague....

I am trying to get a sample of at most 5 random customers from each branch from a table. I can achieve the result using CSUM with a GROUP BY statement pointing to the BRANCH variable and a QUALIFY clause that limits the output to the first 5 customers. I was wondering if there was a method using the SAMPLE statement?

Current SQL using CSUM

     sel *

     from mkting.CIM7403_Customer_Base

     group by planning_srtcd

     qualify csum(1,cidpersid mod 23) < 6

     ;

With SAMPLE I'm aware of the SAMPLE WHEN condition statements that could be used for this purpose but when the number of groups exceeds a 1000 and can change, I was wondering if this could be done using the original table or a lookup to pick all values for the group that I want to set a condition upon and end up with 5 random values per grouping.

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