January 25, 2018 at 2:03 pm
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
January 25, 2018 at 2:27 pm
Just put IsMember at the front of the order by 😀
SELECT TOP 1 * FROM @T ORDER BY IsMember DESC, NEWID()
January 25, 2018 at 7:09 pm
thank you. it worked
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply