Select Random rows WITH running total, possible?

• Joe-397613

  Newbie

  Points: 5

  More actions

  November 13, 2012 at 7:00 pm

  #265414

  I know how to select TOP X random rows from a table using NEWID(). Separately, I know how to select a running total using several methods, CTE, etc.

  BUT, how would one combine these 2 results into one query? So I want to select say 3 random records (AND no less than 3), where the running total does not exceed 15. Can't wrap my head around this one...

  Use this simple table and data:

  CREATE TABLE TblTest (

  id int not null identity(1,1) primary key,

  value int not null

  );

  INSERT INTO TblTest (value) VALUES (4);

  INSERT INTO TblTest (value) VALUES (3);

  INSERT INTO TblTest (value) VALUES (5);

  INSERT INTO TblTest (value) VALUES (6);

  INSERT INTO TblTest (value) VALUES (6);

  INSERT INTO TblTest (value) VALUES (5);

  INSERT INTO TblTest (value) VALUES (6);

  INSERT INTO TblTest (value) VALUES (5);

  INSERT INTO TblTest (value) VALUES (4);

  INSERT INTO TblTest (value) VALUES (7);

  INSERT INTO TblTest (value) VALUES (7);

  INSERT INTO TblTest (value) VALUES (6);

  INSERT INTO TblTest (value) VALUES (5);

  INSERT INTO TblTest (value) VALUES (4);

  My Attempt is below, not sure if it even makes sense having the NEWID there, and sometimes it returns only 2 rows, sometimes 0, I want it to be smart enough to return always 3 rows, and if possible, closest to 15...:

  select ourRandID,

  id,

  value,

  running_total

  from (

  select NEWID() as ourRandID,

  id,

  value,

  sum(value) over (order by NEWID()) as running_total

  from TblTest

  ) t

  where running_total < 16

• Dwain Camps

  SSC Guru

  Points: 86968

  More actions

  November 15, 2012 at 9:08 pm

  #1559597

  Here's a method using the rCTE approach to calculating a running total:

  CREATE TABLE #TblTest (

  id int not null identity(1,1) primary key,

  value int not null

  );

  INSERT INTO #TblTest (value) VALUES (4);

  INSERT INTO #TblTest (value) VALUES (3);

  INSERT INTO #TblTest (value) VALUES (5);

  INSERT INTO #TblTest (value) VALUES (6);

  INSERT INTO #TblTest (value) VALUES (6);

  INSERT INTO #TblTest (value) VALUES (5);

  INSERT INTO #TblTest (value) VALUES (6);

  INSERT INTO #TblTest (value) VALUES (5);

  INSERT INTO #TblTest (value) VALUES (4);

  INSERT INTO #TblTest (value) VALUES (7);

  INSERT INTO #TblTest (value) VALUES (7);

  INSERT INTO #TblTest (value) VALUES (6);

  INSERT INTO #TblTest (value) VALUES (5);

  INSERT INTO #TblTest (value) VALUES (4);

  ;WITH Random3 AS (

  SELECT TOP 3 value

  ,n=ROW_NUMBER() OVER (ORDER BY NEWID())

  FROM #TblTest),

  RunningTotal AS (

  SELECT n, value, total=value

  FROM Random3 WHERE n=1

  UNION ALL

  SELECT b.n + 1, a.value, a.value + total

  FROM Random3 a JOIN RunningTotal b ON a.n = b.n + 1)

  SELECT value, total

  FROM RunningTotal

  DROP TABLE #TblTest

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St