asked on

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

Kyle Abrahams, PMP

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)

ASKER CERTIFIED SOLUTION

FlynnArmstrong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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

Mick_Buckley

ASKER

Thanks that worked a treat

Mick_Buckley

ASKER

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

Kyle Abrahams, PMP

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

Anthony Perkins

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