mysql random from top 10

I have the mysql query:

SELECT * FROM bigtable WHERE column1='1' ORDER BY column2 DESC LIMIT 10

And then I put everything in an array and use php to choose a random row from this array of 10 items.

Is there a way to do this with a single mysql query instead of mysql+php part?

-------------Problems Reply------------

After take top 10, then take 1 with random:

SELECT * from (
SELECT * FROM bigtable
WHERE column1='1'
ORDER BY column2 DESC LIMIT 10
) T ORDER BY RAND()
LIMIT 1

Category:php Views:0 Time:2012-01-25
Tags: php mysql

Related post

  • MySQL: query the top n aggregations 2009-05-26

    I have a table which counts occurrences of one specific action by different users on different objects: CREATE TABLE `Actions` ( `object_id` int(10) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `actionTime` datetime ); Every time a user pe

  • How to get mysql random integer range? 2009-06-11

    I am trying to generate a random integer for each row I select between 1 and 60 as timer. SELECT downloads.date, products.*, (FLOOR(1 + RAND() * 60)) AS timer I have searched and keep coming up to this FLOOR function as how to select a random integer

  • MySQL: Random pick that lasts for a specific period of time? 2010-02-04

    Okay... I'm lost (again). How can I make a custom seed for rand() that will keep the pick from the database for a specific period of time: 10 minutes, 1 hours, 1 day, etc. I use this: $query = 'SELECT * FROM table order by rand () LIMIT 1'; To get a

  • MySql Query: Select top 3 rows from table for each category 2010-06-29

    I have a table with records and it has a row called category. I have inserted too many articles and I want to select only two articles from each category. I tried to do something like this: I created a view: CREATE VIEW limitrows AS SELECT * FROM tbl

  • MYSQL Random Row/Ordering question 2010-12-06

    I am trying to select the three rows that have the highest scores. Obviously I can use a simple things such as: SELECT * FROM myTable ORDER BY myscore DESC LIMIT 3; But what I also want is an easy way for this to return only one of these three rows r

  • mySQL query for top N entries of a grouped records 2010-12-11

    I'm new to MySql and to databases in general. I have a query, which I built together via snippets from online resources and trail and error. It is really slow (27sec) and I assume it can be optimized. Maybe someone could help me out with that. This i

  • mysql random least used 2011-02-12

    I need to select a record at random but not one already selected before unless all records have been selected. Table Setup: _word (id, nam) _word_tm (id, word_id, tm) Every time a word is used it is loaded into _word_tm. What I am wanting to do is ma

  • mysql random with condition 2011-03-23

    I have a table in mysql, say table1. I am running this on it: SELECT FLOOR( MAX(id) * RAND()) FROM `table1` This works well, but I am now trying to add a condition of "AND tom". Where tom is a integer field. For example: id tom 1 0 2 3 3 2 4 0 5 0 6

  • MYSQL random generated id & LOAD DATA LOCAL INFILE 2011-04-24

    is there a way to use LOAD DATA LOCAL INFILE & create a random generated id rather than using the auto increment function? I have tried using MySQL's UUID() but it only produces the same id for each row $sql = "LOAD DATA LOCAL INFILE \"" . $data[

  • How to select maximum of 10 data out of 10,000 from mysql randomly, i.e one at a time using php? 2011-08-03

    Goodday all, please am developing an online game like 'who want to be a millionaire'. I have 10,000 questions with answers, in mysql database and I want the players to answer 10 shufled questions but one at a time with timer.At the end of the ten que

  • MySQL - Random 'A' from Random 'B' 2011-12-14

    From a MySQL table holding records of quotations and their authors (with corresponding unique IDs), I want to select a random record. Simple. Imagine, though, that I have 100 quotes from Shakespeare and only 10 each from other authors. Without using

  • Show mysql random result 2011-12-19

    I have a mysql table named events. Some events are featured. I want to randomly display one of the two latest featured events. The field 'timestamp' holds the UNIX timestamp of the event's creation time. The query looks like this now: $query = "SELEC

  • PHP/MySQL - Random semicolon output 2012-02-15

    I have this script to outpout everything from table called Articles. If I load this script there are random semicolons coming at front of my table like this ; ; This is my code: if($_SESSION['rights'] == 2){ include_once "scripts/mysql123.php"; //men

  • PHP / MySQL - returning the top 10 users by the amount of entries there are 2012-02-23

    I have a database table with a users id and shoe. For each shoe a user gets an row in the db so a person with the id 5 might have 50 entries, a user id of 3 might have 23 entries and so on. Im wondering how i would display the top 10 user ids based o

  • MYSQL: Field in "top level query" unknown in subquery 2012-04-05

    I have a long mysql query which builds a report, the problem I have is that the top level field "a.id" is unknown only to one of the sub queries, this query is a select from a "select with alias". I have set the offending section between comments (hi

  • php mysql query for top 5 average of columns 2012-04-18

    I have a table of rankings for photos. I’d like to output the id of the top 5 photos for each category based on the average of 3 columns (rating1, 2, 3). Say I have the following table columns (photoID,rating1,rating2,rating3) $sth = mysql_query("SEL

  • MySQL random rows sorted by a column name 2009-07-17

    Original Question: I am currently using Zend Framework with Zend_Db_*, and I am selecting three random rows from a table: $category->getTable()->select()->order(new Zend_Db_Expr('RAND()'))->limit('3') Where $category is a Zend_Db_Table_Ro

  • PHP/MySQL - algorithm for "Top Rated" 2009-09-16

    So I just built a star-rating system and and trying to come up with an algorithm to list the "Top Rated" items. For simplicity, here are the columns: item_name average_rating (a decimal from 1 to 5) num_votes I'm trying to determine the "sweet spot"

  • mysql random value change 2009-12-16

    I have a database in mysql with 1 table composed by 5 fields. Two of these fields are FLOAT and generated by RAND function; now i want to change these values each x time, for example numeric values have to change every 0.01 s to simulate a financial

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.156 (s). 11 q(s)