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
Medium Priority
391 Views
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
Question by:doctor069
• 2
• 2
• 2
• +1

LVL 21

Expert Comment

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

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

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

LVL 21

Expert Comment

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

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

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

Author Closing Comment

ID: 37039771
This works best for me.

0

LVL 50

Expert Comment

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

## Featured Post

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
Course of the Month11 days, 20 hours left to enroll