How to get random sample out of a table data that has selective number of variables in a column out of all the variables?
The below query is the one I have used to get random sample of 50,000 rows of a table, by limiting the 50,000 rows only to 7 variables in column2, which are 'zzz', 'xxxx', 'yyyy', 'hhhh', 'ggggg','kkkk','ooo'. But the below query is not working quite good as it is randomly selecting variables amongst the 7 variables and only displaying 2 or 3 variables for the most part. I would like the query to changed in such a way that it shows a 50,000 rows random sample that has all 7 variables in it
select top 50000 v.column1, v.column2, v.column3, v.column 4 from (select top 40 percent distinct ceo.column1, ceo.column2,cm.column 3, cm.column4 from table1 ceo inner join table2 cm on cm.column1 = ceo.column1 where isnumeric(ceo.column1) !=0 and column2 IN ('zzz', 'xxxx', 'yyyy', 'hhhh', 'ggggg','kkkk','ooo' Order by newid()))v
The easiest solution could be an union all
SELECT * from (select top 7000 .... where column2 = 'zzz' ORDER BY newID()) A union all SELECT * from (select top 7000 .... where column2 = 'xxxx' ORDER BY newID()) B
From a performance perspective, you could also tak in consideration SELECT FROM table1 TABLESAMPLE (7000 ROWS)
You can use window functions:
select column1, column2, column3, column4 from (select t.*, row_number() over (partition by column2 order by newid()) as seqnum from t where column2 in ('zzz', 'xxxx', 'yyyy', 'hhhh', 'ggggg', 'kkkk', 'ooo') ) t where seqnum <= 50000;
Your question mentions one table but your code has more than one table -- making the question a bit unclear. However, the same logic would work for a
joinquery in the subquery.