random records from group

Question

I have this table:

Name   ID State
====== == ============
sarah   1 arizona
daniel  2 california 
disney  3 Arizona 
dee     4 wyoming
eee     5 texas
ape     6 oklahoma
eee     7 kansas
csdsd   8 iowa
dfsdf   9 utah
deep   10 northdakota

I want to select 1 random record in a group of 5 and place it in a different table. Is it possible?

For example, for first group(ID 1 to 5) I want to select any ID. Similarly in the second group(6 to 10), I have to select any ID.

#winces# - Lets see, `State` has the following problems - Two different spellings of `'Arizona'` (one is capitalized), `'North Dakota'` lacks the separating space... what are the rest of your entries like? If possible, you should have something like a `State` table that you can foreign-key link to. — Clockwork-Muse, Mar 26 '14 at 00:57

score 1 · Answer 1 · edited May 23 '17 at 12:11

1

Try something like this

insert dbo.bar ( Name, ID , State )
select t.Name , t.ID , t.State
from ( select *  ,
              rownum = row_number()
                      over ( partition by ID / 5 order by newid() ) 
       from dbo.foo
     ) t
where r.rownum = 1

See also the question, Select first row in each GROUP BY group?

edited May 23 '17 at 12:11

Community

1
1

answered Mar 25 '14 at 23:48

Nicholas Carey

71,308
16
93
135

1

I was just about to post this same solution. One thing to point out - this gets partitioned by IDs like this 0-4, 5-9, 10-14 and so on. – attila Mar 26 '14 at 00:05
This returns 3 records for the dataset in the question. – Ron Smith Mar 26 '14 at 00:06

Ron Smith · Answer 2 · 2014-03-26T00:29:54.587

This query will randomly select 1 record for every 5 IDs, no matter how many records are in @YourTable. The assumption is that each record has a consecutive ID.

-- load test data
declare @YourTable table(Name varchar(10),ID int identity(1,1), [State] varchar(20))
insert into @YourTable(Name,[State])
    values  ('sarah','arizona'),
            ('daniel','california'),
            ('disney','Arizona'),
            ('dee','wyoming'),
            ('eee','texas'),
            ('ape','oklahoma'),
            ('eee','kansas'),
            ('csdsd','iowa'),
            ('dfsdf','utah'),
            ('deep','northdakota')

-- randomly select 1 record from every 5 IDs
select ID,Name,[State]
from    (
        select ID,Name,[State],
            row_number() over(partition by (ID-1)/5 order by newid()) rnd
        from @YourTable
        ) r
where rnd = 1

Mahesh Sambu · Answer 3 · 2014-03-25T23:47:20.300

0

Here i am getting the top 1 record from the table just to pick the record from the 1- 5 records. I hope this helps. i have set the counter to 10 , you may set it to the max count you want and increment i have set is 5. you can change it for your wish.

DECLARE @counter smallint;
SET @counter = 1;
WHILE @counter < 10
   BEGIN
      INSERT INTO NEWTABLE              --- your destination table
      ( NAME, ID, STATE)

      SELECT Top 1 * from yourtable where id  between @counter and @counter + 5 

      SET @counter = @counter + 5
   END;

edited Mar 25 '14 at 23:47

answered Mar 25 '14 at 23:32

Mahesh Sambu

349
2
15

This would always pick the first of 5 records -IDs 1, 6, 11, 16, etc. – Ron Smith Mar 26 '14 at 00:26
yeah! this would always get the top 1 record. but it serves purpose so i thought it may be helpful. – Mahesh Sambu Mar 26 '14 at 16:25

random records from group

3 Answers3