Hi,

Is there a query which will select x random unique records out of all records
and sort them randomly, but if all records is smaller than x then it chooses
all the records and sorts them randomly.

e.g choose 3 random unique records out of all records (20) and sort them
randomly (5,1,14) , but if there are only 2 records it chooses the 2 records
and sorts them randomly (2,1).

Thank you in advance,

Raj (Newbie)

raj <raj [at] nospam.com> wrote:
> Raj (Newbie)

Raj, look up what crossposting and multiposting is, and why it's frowned
upon, google 'netiquette', and look at the link in my signature. Many,
many rookie mistakes which might piss regular visitors off, which
otherwise would be glad to help.

Answered in _both_ alt.comp.databases.mysql & comp.databases.mysql.

--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel

Sorry Rik.

Won't happen again.

Raj

On Tue, 6 Mar 2007 08:05:22 +0000, Rik wrote
(in article <op.torcq8kbqnv3q9 [at] misant>):

> raj <raj [at] nospam.com> wrote:
>> Raj (Newbie)
>
> Raj, look up what crossposting and multiposting is, and why it's frowned
> upon, google 'netiquette', and look at the link in my signature. Many,
> many rookie mistakes which might piss regular visitors off, which
> otherwise would be glad to help.
>
> Answered in _both_ alt.comp.databases.mysql & comp.databases.mysql.
>
>

Message-ID: <op.torcq8kbqnv3q9 [at] misant> from Rik contained the following:

>Raj, look up what crossposting and multiposting is, and why it's frowned
>upon

To be fair, /I/ would have been interested in a purely SQL solution even
though I'm only reading php groups. (my solution would have been to
query with a LIMIT 0,n and do the rest with PHP).

However it /is/ preferable to post in one relevant group first and then
widen your search in other groups if you do not get a solution.

--
Geoff Berrow 0110001001101100010000000110
001101101011011001000110111101100111001011
100110001101101111001011100111010101101011

Geoff Berrow <blthecat [at] ckdog.co.uk> wrote:

> Message-ID: <op.torcq8kbqnv3q9 [at] misant> from Rik contained the following:
>
>> Raj, look up what crossposting and multiposting is, and why it's frowned
>> upon
>
> To be fair, /I/ would have been interested in a purely SQL solution even
> though I'm only reading php groups. (my solution would have been to
> query with a LIMIT 0,n and do the rest with PHP).

Well, just to indulge you: the secret is ORDER BY RAND(). And you don't
need the '0,' in the LIMIT statement.

> However it /is/ preferable to post in one relevant group first and then
> widen your search in other groups if you do not get a solution.

Indeed.
--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel

Geoff Berrow wrote:
> Message-ID: <op.torcq8kbqnv3q9 [at] misant> from Rik contained the following:
>
>> Raj, look up what crossposting and multiposting is, and why it's frowned
>> upon
>
> To be fair, /I/ would have been interested in a purely SQL solution even
> though I'm only reading php groups. (my solution would have been to
> query with a LIMIT 0,n and do the rest with PHP).

I would have done everything in SQL, as you never know where the server may be
regarding the web-server, if they are at a long distance from each other, you
really want as little traffic as possible between them.

Using limit will give you in the sql query before the result has been randomly
selected, you will always have a random of a subset of the whole, and that
wasn't what the OP did ask for.


> However it /is/ preferable to post in one relevant group first and then
> widen your search in other groups if you do not get a solution.

Yes, but then you do wait at least more than one minute before posting in
other ng.


--

//Aho

raj <raj [at] nospam.com> wrote:
> Raj (Newbie)

Raj, look up what crossposting and multiposting is, and why it's frowned
upon, google 'netiquette', and look at the link in my signature. Many,
many rookie mistakes which might piss regular visitors off, which
otherwise would be glad to help.

Answered in _both_ alt.comp.databases.mysql & comp.databases.mysql.

--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel

Sorry Rik.

Won't happen again.

Raj

On Tue, 6 Mar 2007 08:05:22 +0000, Rik wrote
(in article <op.torcq8kbqnv3q9 [at] misant>):

> raj <raj [at] nospam.com> wrote:
>> Raj (Newbie)
>
> Raj, look up what crossposting and multiposting is, and why it's frowned
> upon, google 'netiquette', and look at the link in my signature. Many,
> many rookie mistakes which might piss regular visitors off, which
> otherwise would be glad to help.
>
> Answered in _both_ alt.comp.databases.mysql & comp.databases.mysql.
>
>

Message-ID: <op.torcq8kbqnv3q9 [at] misant> from Rik contained the following:

>Raj, look up what crossposting and multiposting is, and why it's frowned
>upon

To be fair, /I/ would have been interested in a purely SQL solution even
though I'm only reading php groups. (my solution would have been to
query with a LIMIT 0,n and do the rest with PHP).

However it /is/ preferable to post in one relevant group first and then
widen your search in other groups if you do not get a solution.

--
Geoff Berrow 0110001001101100010000000110
001101101011011001000110111101100111001011
100110001101101111001011100111010101101011

Geoff Berrow <blthecat [at] ckdog.co.uk> wrote:

> Message-ID: <op.torcq8kbqnv3q9 [at] misant> from Rik contained the following:
>
>> Raj, look up what crossposting and multiposting is, and why it's frowned
>> upon
>
> To be fair, /I/ would have been interested in a purely SQL solution even
> though I'm only reading php groups. (my solution would have been to
> query with a LIMIT 0,n and do the rest with PHP).

Well, just to indulge you: the secret is ORDER BY RAND(). And you don't
need the '0,' in the LIMIT statement.

> However it /is/ preferable to post in one relevant group first and then
> widen your search in other groups if you do not get a solution.

Indeed.
--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel

Geoff Berrow wrote:
> Message-ID: <op.torcq8kbqnv3q9 [at] misant> from Rik contained the following:
>
>> Raj, look up what crossposting and multiposting is, and why it's frowned
>> upon
>
> To be fair, /I/ would have been interested in a purely SQL solution even
> though I'm only reading php groups. (my solution would have been to
> query with a LIMIT 0,n and do the rest with PHP).

I would have done everything in SQL, as you never know where the server may be
regarding the web-server, if they are at a long distance from each other, you
really want as little traffic as possible between them.

Using limit will give you in the sql query before the result has been randomly
selected, you will always have a random of a subset of the whole, and that
wasn't what the OP did ask for.


> However it /is/ preferable to post in one relevant group first and then
> widen your search in other groups if you do not get a solution.

Yes, but then you do wait at least more than one minute before posting in
other ng.


--

//Aho

raj <raj [at] nospam.com> wrote:
> Raj (Newbie)

Raj, look up what crossposting and multiposting is, and why it's frowned
upon, google 'netiquette', and look at the link in my signature. Many,
many rookie mistakes which might piss regular visitors off, which
otherwise would be glad to help.

Answered in _both_ alt.comp.databases.mysql & comp.databases.mysql.

--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel

Sorry Rik.

Won't happen again.

Raj

On Tue, 6 Mar 2007 08:05:22 +0000, Rik wrote
(in article <op.torcq8kbqnv3q9 [at] misant>):

> raj <raj [at] nospam.com> wrote:
>> Raj (Newbie)
>
> Raj, look up what crossposting and multiposting is, and why it's frowned
> upon, google 'netiquette', and look at the link in my signature. Many,
> many rookie mistakes which might piss regular visitors off, which
> otherwise would be glad to help.
>
> Answered in _both_ alt.comp.databases.mysql & comp.databases.mysql.
>
>

Message-ID: <op.torcq8kbqnv3q9 [at] misant> from Rik contained the following:

>Raj, look up what crossposting and multiposting is, and why it's frowned
>upon

To be fair, /I/ would have been interested in a purely SQL solution even
though I'm only reading php groups. (my solution would have been to
query with a LIMIT 0,n and do the rest with PHP).

However it /is/ preferable to post in one relevant group first and then
widen your search in other groups if you do not get a solution.

--
Geoff Berrow 0110001001101100010000000110
001101101011011001000110111101100111001011
100110001101101111001011100111010101101011

Geoff Berrow <blthecat [at] ckdog.co.uk> wrote:

> Message-ID: <op.torcq8kbqnv3q9 [at] misant> from Rik contained the following:
>
>> Raj, look up what crossposting and multiposting is, and why it's frowned
>> upon
>
> To be fair, /I/ would have been interested in a purely SQL solution even
> though I'm only reading php groups. (my solution would have been to
> query with a LIMIT 0,n and do the rest with PHP).

Well, just to indulge you: the secret is ORDER BY RAND(). And you don't
need the '0,' in the LIMIT statement.

> However it /is/ preferable to post in one relevant group first and then
> widen your search in other groups if you do not get a solution.

Indeed.
--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel

Geoff Berrow wrote:
> Message-ID: <op.torcq8kbqnv3q9 [at] misant> from Rik contained the following:
>
>> Raj, look up what crossposting and multiposting is, and why it's frowned
>> upon
>
> To be fair, /I/ would have been interested in a purely SQL solution even
> though I'm only reading php groups. (my solution would have been to
> query with a LIMIT 0,n and do the rest with PHP).

I would have done everything in SQL, as you never know where the server may be
regarding the web-server, if they are at a long distance from each other, you
really want as little traffic as possible between them.

Using limit will give you in the sql query before the result has been randomly
selected, you will always have a random of a subset of the whole, and that
wasn't what the OP did ask for.


> However it /is/ preferable to post in one relevant group first and then
> widen your search in other groups if you do not get a solution.

Yes, but then you do wait at least more than one minute before posting in
other ng.


--

//Aho

raj wrote:

> Is there a query which will select x random unique records out of all records
> and sort them randomly,

To select 10 random records from table "my_table":

SELECT *
FROM my_table
ORDER BY random()
LIMIT 10;


--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!