SELECT a random 10 records from table....easy newbie question

#1

This doesn't work:

--****************************************
USE myDatabase
SELECT TOP 10 *
FROM myTable
WHERE NEWID()
--****************************************

...I know I need to use the function newID() to find random records -
just don't know where it fits in the query (suspect the TOP will need
to go from the above)

Any help greatly appreciated
J

#2

WhytheQ pisze:

Quote:

This doesn't work:

--****************************************
USE myDatabase
SELECT TOP 10 *
FROM myTable
WHERE NEWID()
--****************************************

..I know I need to use the function newID() to find random records -
just don't know where it fits in the query (suspect the TOP will need
to go from the above)

Any help greatly appreciated

Replace "WHERE" with "ORDER BY":

SELECT TOP 10 *
FROM myTable
ORDER BY NEWID()

IMHO this query won't be efficient against large tables.

--
Best regards,
Marcin Guzowski
http://guzowski.info

#3

On Thu, 16 Aug 2007 03:39:49 -0700, WhytheQ wrote:

Quote:

This doesn't work:

--****************************************
USE myDatabase
SELECT TOP 10 *
FROM myTable
WHERE NEWID()
--****************************************

..I know I need to use the function newID() to find random records -
just don't know where it fits in the query (suspect the TOP will need
to go from the above)

Any help greatly appreciated
J

Hi J,

Marcin's answer is correct. However, if you're on SQL Server 2005 you
can also use the new TABLESAMPLE option (see Books Online for the
details); this will probably perform better on large tables.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#4

This comes up with nothing:
'=====================
USE myDatabase
SELECT *
FROM myTable
TABLESAMPLE (1 ROWS)
'====================

....whereas this works:
'=====================
USE myDatabase
SELECT *
FROM myTable
TABLESAMPLE (1 PERCENT)
'====================

Any ideas??

J

On 16 Aug, 21:23, Hugo Kornelis <h... (AT) perFact (DOT) REMOVETHIS.info.INVALID>
wrote:

Quote:

On Thu, 16 Aug 2007 03:39:49 -0700,WhytheQwrote:
This doesn't work:

--****************************************
USE myDatabase
SELECT TOP 10 *
FROM myTable
WHERE NEWID()
--****************************************

..I know I need to use the function newID() to find random records -
just don't know where it fits in the query (suspect the TOP will need
to go from the above)

Any help greatly appreciated
J

Hi J,

Marcin's answer is correct. However, if you're on SQL Server 2005 you
can also use the new TABLESAMPLE option (see Books Online for the
details); this will probably perform better on large tables.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -

- Show quoted text -

#5

Søren Reinke (soren (AT) REMOVE (DOT) reinke.dk) writes:

Quote:

Hugo Kornelis wrote:
Marcin's answer is correct. However, if you're on SQL Server 2005 you
can also use the new TABLESAMPLE option (see Books Online for the
details); this will probably perform better on large tables.

See my signature. Or your hard disk (if you have SQL Server installed).

Quote:

p.s. You blog is to large for pageflakes

(max 512000 bytes) could you
maybe cut down the articles in the feed ?

Pageflakes?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#6

On Fri, 17 Aug 2007 08:12:21 +0200, Søren Reinke wrote:

Quote:

Do you have a link to the book you are talking about ?

Hi Søren,

Erland already adressed this question.

Quote:

p.s. You blog is to large for pageflakes

(max 512000 bytes) could you
maybe cut down the articles in the feed ?

I'll forward this concern to the administrators of the blog. In the
meantime, may I suggest that you read it online?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#7

On Fri, 17 Aug 2007 10:56:10 -0000, WhytheQ wrote:

Quote:

This comes up with nothing:
'=====================
USE myDatabase
SELECT *
FROM myTable
TABLESAMPLE (1 ROWS)
'====================

...whereas this works:
'=====================
USE myDatabase
SELECT *
FROM myTable
TABLESAMPLE (1 PERCENT)
'====================

Any ideas??

Hi J,

TABLESAMPLE won't give you the exact number of rows you asked for, but
an approximation. This is explained in Books Online (though not as
clearly as I hoped and thought it was).

Also see Erland's reply in your other thread.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#8

Erland Sommarskog wrote:

Quote:

Søren Reinke (soren (AT) REMOVE (DOT) reinke.dk) writes:
Hugo Kornelis wrote:
Marcin's answer is correct. However, if you're on SQL Server 2005 you
can also use the new TABLESAMPLE option (see Books Online for the
details); this will probably perform better on large tables.

See my signature. Or your hard disk (if you have SQL Server installed).

Lol thanks

Never thought about looking there, found them

Quote:

p.s. You blog is to large for pageflakes

(max 512000 bytes) could you
maybe cut down the articles in the feed ?

Pageflakes?

http://www.pageflakes.com/

A great way to handle a lot of RSS feeds, and it does not keep
forgetting like Google's does, and is faster than the one from microsoft.

/Søren

#9

On Fri, 17 Aug 2007 08:12:21 +0200, Søren Reinke wrote:

Quote:

p.s. You blog is to large for pageflakes

(max 512000 bytes) could you
maybe cut down the articles in the feed ?

Hi Søren.

Here's the response of Peter DeBetta:

"I just added the main feed to pageflakes without issue. Could you
have this fellow write to me directly so I can work with him on
resolving his issue."

You can use the "Click here to send email" link on the blogs front page
to contact Peter.

Thanks for your interest in sqlblog.com!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SELECT a random 10 records from table....easy newbie question

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

SELECT a random 10 records from table....easy newbie question - 08-16-2007 , 05:39 AM

Re: SELECT a random 10 records from table....easy newbie question - 08-16-2007 , 06:17 AM

Re: SELECT a random 10 records from table....easy newbie question - 08-16-2007 , 03:23 PM

Re: SELECT a random 10 records from table....easy newbie question - 08-17-2007 , 05:56 AM

Re: SELECT a random 10 records from table....easy newbie question - 08-17-2007 , 05:13 PM

Re: SELECT a random 10 records from table....easy newbie question - 08-17-2007 , 05:39 PM

Re: SELECT a random 10 records from table....easy newbie question - 08-17-2007 , 05:44 PM

Re: SELECT a random 10 records from table....easy newbie question - 08-17-2007 , 05:50 PM

Re: SELECT a random 10 records from table....easy newbie question - 08-17-2007 , 06:01 PM