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

Thanks
Mick
Mick_BuckleyAsked:
Who is Participating?
 
FlynnArmstrongCommented:
Seems a bit long winded but seems to work...

SELECT     NAME
FROM         (SELECT     NAME
                       FROM          (SELECT     TOP (3) NAME
                                               FROM          TABLE
                                               WHERE      (NAME <> 'A')
                                               ORDER BY NEWID()) AS derivedtbl_1
                       UNION
                       SELECT     NAME
                       FROM         TABLE AS TABLE_1
                       WHERE     (NAME = 'A')) AS derivedtbl_2
ORDER BY NEWID()
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
http://stackoverflow.com/questions/1400505/postgresql-random-number-range-1-10

Assuming you had a primary key on the rows yes.

Essentially, create 3 variables

@known
@r1
@r2
@r3

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)


0
 
Anthony PerkinsCommented:
A slight simpler variation of the previous one (no points please):

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

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Mick_BuckleyAuthor Commented:
Thanks that worked a treat
0
 
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.

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

0
 
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.