Randomly delete records based on some condition

• vignesh.ms

  Hall of Fame

  Points: 3456

  More actions

  March 19, 2014 at 8:43 am

  #283639

  Hi There,

  create table #sample

  (

  Name varchar(100),

  value int

  )

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('xxx', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('YYY', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('YYY', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('YYY', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('YYY', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('YYY', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('YYY', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  insert into #sample values ('YYY', left(RAND()*(12514/RAND() +93247)/9 *RAND(),2))

  select * from #sample

  Above is the sample records

  From that I wanted to delete some records based on following condition

  * randomly select any number of records but sum(value) = 125 and name = xxx

  Thanks in advance

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  March 19, 2014 at 10:02 am

  #1698435

  What have you attempted thus far?

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Hassie

  SSCommitted

  Points: 1641

  More actions

  March 19, 2014 at 10:53 am

  #1698449

  Can't really see what you are trying to achieve. But the below will delete records based on your WHERE clause requirements

  DELETE FROM X

  FROM (

  select Value, LEAD(Value) OVER (Order By Value) AS RollingSum from #sample where [Name] = 'xxx'

  ) X

  WHERE x.RollingSum <= 125