• Status: Solved
• Priority: Medium
• Security: Public
• Views: 395

# 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

0
doctor069
• 2
• 2
• 2
• +1
1 Solution

Commented:
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

0

Author Commented:
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?
0

Commented:
tablesample won't give you 3 by weight. I think newid() is your best bet, but you can do it like this

select * from
(
select *, row_number() over (partition by weight order by newid()) rn
from Lines
) a
where rn <= 3
0

Commented:
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

0

Commented:
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
0

Commented:
Lowfatspread, did you see my comment?
0

Author Commented:
This works best for me.

0

Commented:
@ramalda   no not when i picked the problem up...
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.