How to choose the number of records based on the count in the subquery

Question

I need to return X the number of records in the table based on the number of records in the subquery.

For example, if the TOP 80 PERCENT records in MYTABLE are 275 records, then I want to select 275 records from another table.

Is it possible to do this with simple dynamic SQL and without creating variables, etc.

My predecessor wrote something like this:

DECLARE @RecordVariable int SET @RecordVariable = (SELECT COUNT(*) * .8 FROM MYTABLE) SELECT TOP (@RecordVariable) * FROM MYOTHERTABLE ORDER BY NEWID() 

Answer 1

You can nest a query in TOP ()

 SELECT TOP (Select cast(count(*)*.8 as int) From MYTABLE) * FROM MYOTHERTABLE ORDER BY NEWID() 

EDIT - Faster Random Picks

Here is one way to increase speed with TABLESAMPLE

 SELECT TOP (Select cast(count(*)*.8 as int) From MYTABLE) * FROM MYOTHERTABLE TABLESAMPLE (10000 ROWS) -- could be (50 PERCENT) ORDER BY NEWID() 

This will result in a random block of 100,000 rows (change as desired) and then return the top order N with NewID ()

Answer 2

There is no need for dynamic SQL, I would not have thought.

 select top (select cast((count(*) * .8)as int) from YourTable) * from YourTable order by NEWID()