Prob w/ RANDOM in a query

[BennyLinton]

I am using the SQL below to successfully return a set of 10% of my total eligible, however I need to change this to 2% but it returns NO records... puzzled:

INSERT INTO Random_Temp ( indx, peopleId, audited )
SELECT TOP 10 PERCENT b.indx, b.peopleId, -1 AS audited
FROM dbo_Billing AS b
ORDER BY Rnd(-(1000*b.indx)*Time());

 

[theDBguy]

How did you change it? Did you simply change 10 PERCENT to 2 PERCENT and left everything else the same?

 

[BennyLinton]

Yes just changed the 10 to a 2.

 

[arnelgp]

that only means that your record count is below 50.

2% of 50 records = 1 record!

 

[BennyLinton]

My record count is 70 which with the original code returned 7 records.

 

[arnelgp]

it should at least retrieve 1 record?

2% of 70 records = 1.4 records!

so why not use Top 1 or Top 2.

 

[BennyLinton]

Because the record set will vary and it needs to automatically return 2%.

 

[BennyLinton]

Should I have altered this line also?:

ORDER BY Rnd(-(1000*b.indx)*Time());

 

[MajP]

The index is needed to get a unique value per row and the time is good to get a unique sequence. However the - 1000 does nothing but does not hurt