three random rows along with a known row

Hi, i have a table with 100 rows. i would like to be able to run a query that would return three random rows along with a fourth known row. the randoms rows would obviously not include the know row. The known row will be different on each query.

I know i can get the random rows with the following
select top 3 name from table where name <> 'A' order by newid()

if that returned C, M, Z for instance i would like the "known row" 'A' to be in the results, but all four results in a random order so the "known row" wasn't always in the same position.

is this possible

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:

Assuming you had a primary key on the rows yes.

Essentially, create 3 variables


select @r1 = @known, @r2=@konwn, @r3=@known.

--in case random returns known.
while @r1 = @known
   set @r1 = (select random() * 100 + 1 )

while @r2 = @known
   set @r2 = (select random() * 100 + 1 )

while @r3 = @known
   set @r3 = (select random() * 100 + 1 )

select * from <table> where id in (@r1,@r2, @r3, @known)

Seems a bit long winded but seems to work...

FROM         (SELECT     NAME
                       FROM          (SELECT     TOP (3) NAME
                                               FROM          TABLE
                                               WHERE      (NAME <> 'A')
                                               ORDER BY NEWID()) AS derivedtbl_1
                       SELECT     NAME
                       FROM         TABLE AS TABLE_1
                       WHERE     (NAME = 'A')) AS derivedtbl_2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
A slight simpler variation of the previous one (no points please):

FROM    ((
      SELECT TOP (3)
      FROM      YourTable
      WHERE     NAME <> 'A'
      ORDER BY  NEWID())
      FROM   YourTable
      WHERE  NAME = 'A'
        ) a

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Mick_BuckleyAuthor Commented:
Thanks that worked a treat
Mick_BuckleyAuthor Commented:
Thanks for all the replies.

ged325, your solution sometimes only returned three rows instead of four, not sure why.

acperkins, i get the following error with your solution.
ORDER BY items must appear in the select list if the statement contains a UNION operator

Thanks again.

Kyle AbrahamsSenior .Net DeveloperCommented:
Mick:  could be that the IDs were the same.  Thanks for getting back to me.

Anthony PerkinsCommented:
>>ORDER BY items must appear in the select list if the statement contains a UNION operator<<
Good point.  I must have had a senior moment.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.