asked on

SQL Random

Hi -

I have a table that hasy the following rows

id | comment| weight

- The weight is measure between 0 and 5
- The table has about 5000 rows

I need to select
3 random rows with weight 0 then
3 random rows with weight 1 then
3 random rows with weight 3 then ... right up to 5

I am reading that ORDER BY newid() would be to heavy on the DB

Does anyone know the most efficient way to do this?

Thanks

JestersGrind

I think you would be fine with using ORDER BY NEWID(). 5000 records is still considered a small table. Your other option is the use TABLESAMPLE. Take a look at this MS article.

http://msdn.microsoft.com/en-us/library/ms189108.aspx

Greg

doctor069

ASKER

So the best would be:

SELECT TOP 3 * FROM [dbo].[Lines] where [weight]='0' ORDER BY newid()
SELECT TOP 3 * FROM [dbo].[Lines] where [weight]='1' ORDER BY newid()
SELECT TOP 3 * FROM [dbo].[Lines] where [weight]='2' ORDER BY newid()
SELECT TOP 3 * FROM [dbo].[Lines] where [weight]='3' ORDER BY newid()
SELECT TOP 3 * FROM [dbo].[Lines] where [weight]='4' ORDER BY newid()
SELECT TOP 3 * FROM [dbo].[Lines] where [weight]='5' ORDER BY newid()

Putting them into a tmp table and then selecting from there?

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

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

Start Free Trial

JestersGrind

You could put them into a temp table if you need to do more with it or you could UNION the results together if you are only interested in returning the results.

Greg

Lowfatspread

i'm unclear why order by newid would give concern...
do you have an article?

i'd suggest

select *
from (
select *
,row_number() over (partition by weight order by newid()) as rn
from dbo.lines
) as x
where rn<=5
order by weight,rn

ralmada

Lowfatspread, did you see my comment?

doctor069

ASKER

This works best for me.

Thanks for your help

Lowfatspread

@ramalda no not when i picked the problem up...