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

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
Asked:
doctor069
  • 2
  • 2
  • 2
  • +1
1 Solution
 
JestersGrindCommented:
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
 
doctor069Author 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
 
ralmadaCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JestersGrindCommented:
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
 
LowfatspreadCommented:
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
 
ralmadaCommented:
Lowfatspread, did you see my comment?
0
 
doctor069Author Commented:
This works best for me.

Thanks for your help
0
 
LowfatspreadCommented:
@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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now