How to select number of record based on a count in a subquery

Question

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

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

Can this be done with just plain 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

No need for dynamic SQL either, I wouldn't think.

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

Answer 2

You can nest the query within the TOP ()

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

EDIT - Speed-Up Random Select

Here is one method to increase the speed using 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 essentially take a random block of 100,000 rows (change as desired), and then return top N order by NewID()