LinuxQuestions.org
View the Most Wanted LQ Wiki articles.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 01-11-2011, 08:17 AM   #1
brianmcgee
Member
 
Registered: Jun 2007
Location: Munich, Germany
Distribution: RHEL, CentOS, Fedora, SLES (...)
Posts: 399

Rep: Reputation: 38
[MySQL] Get a random selection of an ordered data set


Given the following data structure:
Quote:
data_table
ID | Data | Pos
01 | AAAA | 1
02 | ABAB | 4
03 | AABB | 1
04 | BABA | 4
05 | BBBB | 2
06 | AAAB | 2
07 | ABBB | 3
08 | .... | n
What is the minimum possible MySQL query to get a random data selection that includes each Index number only once and ordered ascending. The maximum index number is known.

For example running the query should get the following data: 01, 06, 07, 02.

Running the query again, you may get the same data or another possible combination like: 01, 05, 07, 04.

Of course you could run a loop for 1 to max_index and process a random ordered data selection until the next index number is found. But I think this will result in too many database access.

Also you could run max_index queries to get all IDs that correspond to the given index and randomly select one ID for each index.

But what will be the most elegant way?

Last edited by brianmcgee; 01-12-2011 at 04:42 AM. Reason: renamed Index -> Pos as Index is a reserved word.
 
Old 01-11-2011, 08:30 AM   #2
darkduck
Member
 
Registered: Jan 2011
Location: London
Distribution: Mageia, Debian, Mint
Posts: 490

Rep: Reputation: 26
Will LIMIT statement do what you need?
http://dev.mysql.com/doc/refman/5.5/en/select.html
 
Old 01-11-2011, 08:49 AM   #3
brianmcgee
Member
 
Registered: Jun 2007
Location: Munich, Germany
Distribution: RHEL, CentOS, Fedora, SLES (...)
Posts: 399

Original Poster
Rep: Reputation: 38
Limit would be of use:
Code:
pseudo code:

for i in range(max_index):
    id[i] = mysql_query("SELECT ID FROM data_table WHERE max_index = i ORDER BY RAND() LIMIT 1;")
selection = ",".join(id[])
But I would run max_index mysql queries with maybe lots of returned data for each index just to get one possible set.

Maybe this may be done with a stored procedure?

Still looking for a hint for a smooth solution.

max_index may be a large number and there may be lots of duplicates for each index.

Last edited by brianmcgee; 01-11-2011 at 08:51 AM.
 
Old 01-12-2011, 04:41 AM   #4
brianmcgee
Member
 
Registered: Jun 2007
Location: Munich, Germany
Distribution: RHEL, CentOS, Fedora, SLES (...)
Posts: 399

Original Poster
Rep: Reputation: 38
Yesterday I found the following solution:

Code:
SELECT r.Pos, (SELECT tbl_test.Data FROM tbl_test WHERE r.Pos=tbl_test.Pos ORDER BY rand() LIMIT 1) AS 'output' FROM tbl_test r GROUP BY r.Pos;
 
  


Reply

Tags
mysql, rnd, selection


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL results arbitrarily ordered hbar Programming 12 10-19-2009 07:40 PM
LXer: Brain Teaser: Seemingly Random Number List Selection LXer Syndicated Linux News 0 09-10-2008 12:41 PM
dpkg --set-selection package install status question kushalkoolwal Debian 2 06-19-2008 03:19 PM
Scripting question concerning random selection of a phrase from a database cjaysson Linux - Newbie 2 11-28-2007 03:53 AM
What is the definition of Full Data and Ordered Data? WhiteTornado Linux - Software 2 01-30-2004 04:07 PM


All times are GMT -5. The time now is 12:17 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration