Using TSQL to select a percentage of the total sum of records at random

Question

AVADHESH PATEL · Answer

Hi Mark!Messy, but it seems to work--Create a temp table and add a random number columnCREATE TABLE #Roads(ROW_NUM int, RoadID int, RoadLength int)--Populate from zt_Roads table and add a random number fieldINSERT #Roads (ROW_NUM , RoadID , RoadLength ) (SELECT ROW_NUMBER() OVER (ORDER BY NEWID()), RoadID, RoadLength from zt_Roads)go--Calcualte 5% of the TOTAL length of ALL roadsdeclare @FivePercent intSELECT @FivePercent = ROUND(Sum(IsNULL((RoadLength ),0))*.01,0) from zt_Roadsprint 'One Percent of total length = ' Print @FivePercent--Select a random sample from temp table so that the total sample length --is no more than 5% of all roads in table; with RandomSample as (SELECT top 100 percent ROW_NUM, RoadID, RoadLength, RoadLength+ COALESCE((Select Sum(RoadLength) from #Roads b WHERE b.ROW_NUM &lt; a.ROW_NUM),0) as RunningTotal From #Roads a ORDER BY ROW_NUM)Select * from RandomSample WHERE RunningTotal &lt;@FivePercent Drop table #Roads

forum

Using TSQL to select a percentage of the total sum of records at random

Can you answer this question?

1 Answers