CODEWITHSUNDEEP
sqlsql-server
user3325141
user3325141

Reputation: 89

random records from group

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.

Upvotes: 0

Views: 70

Answers (3)

Ron Smith
Ron Smith

Reputation: 3266

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

Upvotes: 1

Nicholas Carey
Nicholas Carey

Reputation: 74385

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?

Upvotes: 1

Mahesh Sambu
Mahesh Sambu

Reputation: 349

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;

Upvotes: 0

Related Questions