Top 1 record order by column and random

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 25, 2018 at 2:03 pm

  #326505

  Hi,
  Please find the below sample records to play with. basically i am looking to get top 1  ramdomized  record. assume in my actual table i have 100*100 records. the tricky here is first  randomization has to apply for the condition IsMember = 1.  if there is no IsMember = 1 then randomization can apply to IsMember = 0.
  Declare @T Table(Id int identity(1,1), name varchar(100),IsMember bit)

  insert into @T(name,IsMember)

  select 'Tom1', 1 union all
select 'Tom2', 1 union all
select 'Tom3', 1 union all
select 'Tom4', 1 union all
select 'Tom5', 1 union all
select 'Tom6', 0 union all
select 'Tom7',0 union all
select 'Tom8', 1 union all
select 'Tom9', 0 union all
select 'Tom10', 1 union all
select 'Tom11', 0

  any best way to achieve this. tried googling and got advice to use order by newid(). but i am not sure  how to apply the order by  for IsMember conditional logic of my records. any sample query please

• ZZartin

  SSC-Dedicated

  Points: 30921

  More actions

  January 25, 2018 at 2:27 pm

  #1977437

  Just put IsMember at the front of the order by 

  SELECT TOP 1 * FROM @T ORDER BY IsMember DESC, NEWID()

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 25, 2018 at 7:09 pm

  #1977466

  thank you. it worked