display different sort order for each execution

• mxy

  SSCarpal Tunnel

  Points: 4036

  More actions

  July 18, 2016 at 5:14 pm

  #315249

  Hello all,

  i would like to know if there is a way to display results in random order for each execution.

  /*-------------------------Drop temp table-------------------------------------------*/

  IF OBJECT_ID('tempdb..#temp') IS NOT NULL

  DROP TABLE #temp;

  create table #temp (col1 int, col2 varchar(4))

  insert into #temp

  select 7, 'AB'

  UNION SELECT 2, 'CD'

  UNION SELECT 9, 'JQ'

  UNION SELECT 12, '3A'

  UNION SELECT 3, '8D'

  UNION SELECT 1, 'ER'

  SELECT * FROM #TEMP

  i want to display results in different sort order each time is it possible?

• pietlinden

  SSC Guru

  Points: 63552

  More actions

  July 18, 2016 at 5:25 pm

  #1890743

  Nothing to it

  SELECT * FROM #TEMP

  ORDER BY NEWID();

  NEWID() generates a random number for each record, so sorting by it causes a "random" sort.

• Eirikur Eiriksson

  SSC Guru

  Points: 182943

  More actions

  July 18, 2016 at 11:52 pm

  #1890763

  mxy (7/18/2016)

  ---

  Hello all,

  i would like to know if there is a way to display results in random order for each execution.

  Out of curiosity, why do you want to do that?

  

  As Pieter pointed out, this is very simple to do but can be quite hefty work for the server if the result set is large.

• mxy

  SSCarpal Tunnel

  Points: 4036

  More actions

  July 19, 2016 at 6:07 pm

  #1890974

  i have store data and requirement for monthly promotions is show promotions in a random order

  not leaning towards one brand