Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Random

Posted on 2011-10-27
8
Medium Priority
?
391 Views
Last Modified: 2012-05-12
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
Comment
Question by:doctor069
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 37039527
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 Comment

by:doctor069
ID: 37039660
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
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 37039680
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 21

Expert Comment

by:JestersGrind
ID: 37039698
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37039704
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
 
LVL 41

Expert Comment

by:ralmada
ID: 37039739
Lowfatspread, did you see my comment?
0
 

Author Closing Comment

by:doctor069
ID: 37039771
This works best for me.

Thanks for your help
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37039996
@ramalda   no not when i picked the problem up...
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question