Re: MySQL: Ordering Random Records - Ignatius Reilly - com.mysql.lists.win32

15 messages in com.mysql.lists.win32Re: MySQL: Ordering Random Records

From	Sent On	Attachments
Stuart M. Robinson	05 Oct 2003 10:15
Ignatius Reilly	05 Oct 2003 11:00
Stuart M. Robinson	05 Oct 2003 12:14
Ignatius Reilly	05 Oct 2003 14:33
Stuart M. Robinson	06 Oct 2003 13:13
Ignatius Reilly	06 Oct 2003 14:13
Stuart M. Robinson	06 Oct 2003 15:24
Ignatius Reilly	06 Oct 2003 22:39
Stuart M. Robinson	07 Oct 2003 13:26
Stuart M. Robinson	07 Oct 2003 13:29
Stuart M. Robinson	07 Oct 2003 16:00
jbon...@sola.com.au	07 Oct 2003 18:08
Matt W	07 Oct 2003 21:59
Stuart M. Robinson	04 Dec 2003 01:01
Armando	04 Dec 2003 04:49

Subject:	Re: MySQL: Ordering Random Records
From:	Ignatius Reilly (igna...@free.fr)
Date:	10/05/2003 02:33:09 PM
List:	com.mysql.lists.win32

OK, Stuart, I understand now what you want to do.

You can not do it in one step (subqueries not yet supported). Therefore:

CREATE TEMPORARY TABLE T1 SELECT record1, ... ORDER BY RAND() LIMIT 20

then

SELECT ... FROM T1 ORDER BY record1

HTH Ignatius

_________________________

Ignatius,

Thanks for your input, but as I mentioned in the original message, your idea (and mine) doesn't work, it fails to randomise the records and, I think, the random part of the SQL should come first.

To reiterate, I want to get twenty random records from the database, then order them by record1 so that those random records are then displayed in alphabetic order.

If I use - ORDER BY record1, RAND() - the SQL pulls twenty records out of the database, listed in alphabetical order, but they're the same records as produced by the command - ORDER BY record 1 ASC.

Stuart.

At 20:00 05/10/2003 +0200, Ignatius Reilly wrote:

If you want to sort by 1) record1, 2) random order for all records having the same record1 value, then you should do:

ORDER BY record1, RAND()

Ignatius

_________________________

Folks,

Time to consult the experts, this seems to have everyone stumped but it can't be that hard!

I'd like to pull twenty random records from a MySQL database using ASP VBScript. That's fairly easy:

rsTest.Source = "SELECT record1, record2, record3 FROM Mydatabase.Mytable ORDER BY RAND();"

...but then, I'd like to order those records alphabetically by to record1.

ORDER BY RAND(), record1 ASC

...gets me the twenty records but they're not ordered at all, they're still random.

Reversing the command, thus:

ORDER BY disclinkname ASC, RAND()

...also gets me twenty records, in order, and they're no longer random. So how does one combine the two? Any pointers would be greatly appreciated. Thanks!

Stuart.

http://lists.mysql.com/win32?unsub=robi...@easynet.co.uk