Random ordered groups

• Andy Lucas

  SSC Veteran

  Points: 295

  More actions

  January 23, 2011 at 10:58 am

  #245730

  CREATE TABLE #TESTSORTING (ID INT ,GroupedCol INT)

  INSERT INTO #TESTSORTING

  SELECT 1,1 UNION ALL

  SELECT 2,2 UNION ALL

  SELECT 3,1

  SELECT 'This is the NON desired output:' comment

  SELECT * FROM #TESTSORTING ORDER BY ID ASC

  SELECT 'This is the actual output that CAN (but must not) include the above:' comment

  SELECT * FROM #TESTSORTING ORDER BY NEWID()

  Drop Table #TESTSORTING

  What I am trying to do is this;

  When you use order by [col] you effectively "group" rows when those [col]'s hold the same data in other rows. I want to randomize those "groups" in the returned table, so in my simplistic example the rows returned in GroupedCol would be

  1

  1

  2

  Or

  2

  1

  1

  Never

  1

  2

  1

  Many thanx for your thoughts,

  Andy

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 23, 2011 at 11:35 am

  #1276993

  so correct me if i'm wrong, but you might want a sampling, like a random top 3 per group, right?

  if that is the case, it's deceptively simple...a CTE and newid can get you there.

  here's an example you can run multiple times..it is just the 3 objects per object type from your sys.objects:

  WITH

  cteRandomObject AS

  (

  SELECT obj.Object_ID,

  obj.name as ObjectName,

  obj.type_desc,

  ROW_NUMBER() OVER (PARTITION BY obj.type_desc ORDER BY NEWID()) AS RowNum

  FROM sys.objects obj

  )

  SELECT *

  FROM cteRandomObject

  WHERE RowNum <= 3

  ORDER BY type_desc

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Andy Lucas

  SSC Veteran

  Points: 295

  More actions

  January 24, 2011 at 2:20 am

  #1277081

  Lowell (1/23/2011)

  ---

  so correct me if i'm wrong, but you might want a sampling, like a random top 3 per group, right?

  No that is not correct.

  There are any number of "groups" (ie GroupedCol with identical values)

  with any number of members

  Those "groups" need to be returned in a random order

  CREATE TABLE #TESTSORTING (ID INT ,GroupedCol INT)

  INSERT INTO #TESTSORTING

  SELECT 1,1 UNION ALL

  SELECT 2,2 UNION ALL

  SELECT 3,2 UNION ALL

  SELECT 4,3 UNION ALL

  SELECT 5,3 UNION ALL

  SELECT 6,4 UNION ALL

  SELECT 7,4 UNION ALL

  SELECT 8,4 UNION ALL

  SELECT 9,1

  SELECT * FROM #TESTSORTING ORDER BY GroupedCol

  Drop Table #TESTSORTING

  For Example like this IDGroupedCol

  64

  74

  84

  22

  32

  11

  91

  43

  53

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  January 24, 2011 at 5:10 am

  #1277134

  How about this:

  ;WITH RANDOMIZER AS

  (

  SELECT GroupedCol, MIN(ABS(CHECKSUM(NEWID()))) NWID

  FROM #TESTSORTING

  GROUP BY GroupedCol

  )

  SELECT TS.*

  FROM #TESTSORTING TS

  INNER JOIN RANDOMIZER RND

  ON TS.GroupedCol = RND.GroupedCol

  ORDER BY RND.NWID , TS.GroupedCol

  This may be ugly, but this does it 😛

• Andy Lucas

  SSC Veteran

  Points: 295

  More actions

  January 24, 2011 at 7:53 am

  #1277207

  Fantastic!

  Thanks very much ColdCoffee

  It is already working in the live environment 🙂

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  January 24, 2011 at 8:37 am

  #1277250

  Wow, thanks for the feedback Lucas.. Hope u understood what the code does 🙂

• Andy Lucas

  SSC Veteran

  Points: 295

  More actions

  January 24, 2011 at 12:04 pm

  #1277408

  Actually ColdCoffee, I am trying to get my head around it;

  If you can explain I would really appreciate it - thanx, Andy

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  January 24, 2011 at 6:58 pm

  #1277563

  The below chunk will assign a unique number to each group (here as we are using GROUP BY, we will have unique values from GroupedCol column). NEWID() will produce UNIQUE GUID for each unique row in GroupedCol, CHECKSUM makes it an INTEGER (may be positive or negative), ABS makes the CHECKSUM's results to positive number, while MIN prevents GROUP BY from assigning producing UNIQUE GUID to each row from #TESTSORTING but only unique rows.

  ;WITH RANDOMIZER AS

  (

  SELECT GroupedCol, MIN(ABS(CHECKSUM(NEWID()))) NWID

  FROM #TESTSORTING

  GROUP BY GroupedCol

  )

  The below chunk will then JOIN with the original table and give the new unique number to each row and then finally does an ORDER BY on RND.NWID to get the result in the order of the unique number ; as the unique number will vary during each execution you are assured of a new result set at every execution. TS.GroupedCol is added to the GROUP BY for robustness.

  SELECT TS.*

  FROM #TESTSORTING TS

  INNER JOIN RANDOMIZER RND

  ON TS.GroupedCol = RND.GroupedCol

  ORDER BY RND.NWID , TS.GroupedCol

  Hope this helps.

  One advice (i know people don't like advices, but i will have to tell it out :(), Please do not use a code in LIVE environment if u r not sure of what it does. I sincerely hope you have tested the code enough to be able to put it LIVE 🙂

• Jeff Moden

  SSC Guru

  Points: 1004414

  More actions

  January 24, 2011 at 7:00 pm

  #1277565

  ColdCoffee (1/24/2011)

  ---

  One advice (i know people don't like advices, but i will have to tell it out ), Please do not use a code in LIVE environment if u r not sure of what it does.

  I wish more people would go by that very good advice, CC. 🙂

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  January 24, 2011 at 7:04 pm

  #1277568

  Jeff Moden (1/24/2011)

  ---

  ColdCoffee (1/24/2011)

  ---

  One advice (i know people don't like advices, but i will have to tell it out ), Please do not use a code in LIVE environment if u r not sure of what it does.

  I wish more people would go by that very good advice, CC. 🙂

  Even i sincerely hope that Jeff. Oh Jeff, BTW, Happy New Year. I was so busy lately i wasn't able to wish our good folks here. In fact i have been so busy i dint log into SSC as i used to before 🙁

• Jeff Moden

  SSC Guru

  Points: 1004414

  More actions

  January 24, 2011 at 7:13 pm

  #1277571

  Indeed. Happy New Year to you, as well. It's going to be a great year. 🙂

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Andy Lucas

  SSC Veteran

  Points: 295

  More actions

  January 25, 2011 at 6:29 am

  #1277786

  Hope this helps.

  One advice (i know people don't like advices, but i will have to tell it out :(), Please do not use a code in LIVE environment if u r not sure of what it does. I sincerely hope you have tested the code enough to be able to put it LIVE 🙂

  Thanks ColdCoffee,

  I did kind of get there in the end and i had already tested the snippet inside out, back to front and upside down 😀

  The "live" environment is not mission critical just a subjective "taste" thing on a website (the client preferred things listed in a random way as opposed to an alphanumeric way)

  It was really the the functions you used to alter NEWID() that I was not familiar with but I looked them up on CHC (formerly BOL)

  Thanks once again and please never hold back on advice, certainly for me anyway, I need all I can get!

  Regards,

  Andy

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  January 25, 2011 at 9:27 am

  #1277967

  Andy Lucas (1/25/2011)

  ---

  Hope this helps.

  One advice (i know people don't like advices, but i will have to tell it out :(), Please do not use a code in LIVE environment if u r not sure of what it does. I sincerely hope you have tested the code enough to be able to put it LIVE 🙂

  Thanks ColdCoffee,

  I did kind of get there in the end and i had already tested the snippet inside out, back to front and upside down 😀

  The "live" environment is not mission critical just a subjective "taste" thing on a website (the client preferred things listed in a random way as opposed to an alphanumeric way)

  It was really the the functions you used to alter NEWID() that I was not familiar with but I looked them up on CHC (formerly BOL)

  Thanks once again and please never hold back on advice, certainly for me anyway, I need all I can get!

  Regards,

  Andy

  Wow, Thanks Andy.. You sure must be one cool guy! Fantastic! 🙂