There is a table in which random integers are written. It is necessary to group the numbers by hundreds and from each hundred select 10 random values.

Here is an example of a request from the table:

SELECT TOP 10 [ID] ,[Phone] FROM [CallList].[dbo].[hundreds] 
 ID Phone ----------- ------ 1 210001 2 210002 3 210003 4 210004 5 210006 6 210007 7 210008 8 210009 9 210010 10 210011

Next, I evolved to:

 SELECT *,ROW_NUMBER() OVER (PARTITION BY hundr order by hundr) as 'num' FROM ( SELECT id, phone, cast(phone/100 as int) as 'hundr' FROM [CallList].[dbo].[hundreds]) x

How out of what came out to get 10 random numbers from each hundred?

  • What is the actual problem? Number in row_number in the partition and in the group by add hundreds, a function similar to the random in sql is. max (case when number = random then value end) allows you to select. Give an example of your sample. For you, the decision will not write. - nick_n_a
  • @nick_n_a corrected the question. but I still don’t understand how to choose 10 random ones from what came out. - Andrew Romanenko

1 answer 1

Try this option, of course, before this, replacing YOURTABLE with the desired table and * with the correct set of columns.

NEWID () is best suited for generating a random order in a sample. Then we number each line in the sample, and the last step is to select the first 10 lines from each hundred.

EDIT:

Corrected the answer after editing the question. Again, replace * with the desired set of columns. Added sorting by hundr and RandomId at first, in order to randomize not every sample, but every hundred lines. Then we number the result again and select the first 10 of each hundred:

 ;WITH OrderedRandomSet AS ( SELECT TOP 100 PERCENT NEWID() AS RandomId, CAST(phone/100 AS INT) AS 'hundr', * FROM [CallList].[dbo].[hundreds] ORDER BY hundr, RandomId ) ,NumberedRandomSet AS ( SELECT ROW_NUMBER ( ) OVER(ORDER BY hundr, RandomId) AS RowNumber, * FROM OrderedRandomSet ) SELECT * FROM NumberedRandomSet WHERE (RowNumber % 100) BETWEEN 1 AND 10 ORDER BY RowNumber
  • MS SQL platform. More specific question. - Andrew Romanenko
  • Corrected the answer. - Vadim K.
  • If it helped - mark as answered :) - Vadim K.