Trying To Understand RAND() And LIMIT
I have been doing some queries using a combination of factors that include RAND() and LIMIT.
My code looks like this:
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
MySQL Error Unsure Of The Cause And Need To Understand
I keep getting an error with an INSERT that I am doing. This is my statement: MySQL $inssql = "INSERT INTO events(date, start, end, name, desc) VALUES(" . "'" . $_POST['date'] . "', '" . $finalstart . "', '" . $finalend . "', '" . addslashes($_POST['name']) . "', '" . addslashes($_POST['desc']) . "');"; The data to be inserted is from an XHTML form, and is being processed with php. This is the error that I keep getting: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc) VALUES(��-9-26', ༽:00:00', ཁ:00:00', 'hkjgdslh', 'nksnglkjshgjlkds'' at line 1 I'm not sure why I am getting this, can someone help to explain it to me?
Rand()?
I use this query to return the three fields of all providers in my database. PHP $result = mysql_query(" SELECT Approve, ID, Name FROM PROVIDER WHERE (CURRENT_DATE BETWEEN From_Date AND To_Date OR From_Date IS NULL) AND Approve = Ƈ' ORDER BY ID ASC LIMIT $from, $max_results "); I'd like to instead return a random list of the providers, how do I do that? (like this?) PHP $result = mysql_query(" SELECT Approve, ID, Name FROM PROVIDER WHERE (CURRENT_DATE BETWEEN From_Date AND To_Date OR From_Date IS NULL) AND Approve = Ƈ' ORDER BY rand(ID) LIMIT $from, $max_results ");
FIRST() And RAND()
I have two tables: Sport (Id, Name) SportPhoto (Id, SportId, Active) (the actual photos are stored elsewhere...) I want a VIEW and/or PROCEDURE that will get a the list of sports with a random photo for each. I need something like this: SELECT FIRST(Id ORDER BY RAND()) Id, SportId FROM SportPhoto WHERE ACTIVE = 1 GROUP BY SportId; where that would get the list of sports with one random photo for each sport. I guess there's no FIRST() aggregate function in MySQL. So, how would I do what I need to do?
Order By If(rand()....)
I'm trying to return a list of products, where the top of the list will be one of 2 products, the choice being randomly selected. I tried a rand() condition in the ORDER BY clause, which partly works, but of course the rand() function is evaluated for every row, so it doesn't give the consistent results i need. SELECT productID, prodName, adMessage, nPrice, nPrice*(1+vatRate) as salePrice, showSale, originalPrice, pic.picType, pic.ordering FROM tProducts p JOIN pictures pic ON p.productID=pic.relProdId and pic.picExists = 1 JOIN taxCodes tax ON p.taxCode=tax.taxCode WHERE p.featuredProduct=1 AND p.stocklevel>-3 GROUP BY p.productID ORDER BY productID=if(rand()<0.5,117029,117035 ) desc, rand() LIMIT 10
RAND Function
Basically, I have a stored function that is designed to deliver a random 3 digit code. But I need it to be of the format #~#~# (ie, 3~0~7, 5~1~5, etc) So I set up the function to generate 3 random digits and then concatenate them together, using the following lines inside the function: SET v1 = FLOOR(RAND() * 10); SET v2 = FLOOR(RAND() * 10); SET v3 = FLOOR(RAND() * 10); SET vCode = CONCAT_WS('~', v1, v2, v3); The function works fine, and gives me back my 3 digit code in the exact format that I want. However, after testing it out, I've discovered that it isn't entirely random. There are certain combinations that I never end up getting returned from the function. There are basically 1000 different possible combinations, but I'm only ever receiving 800 combinations back. I've loop tested the function to generate literally millions and millions of different codes, and there are certain codes that I just never see. Code:
Using Rand() Within Categories
Supposed the database looks like this: image_id, category_id, description, and for each category_id a, b, c, d, e, and f (6 categories), there are 5 images (5 image_ids). And i would like to select randomly one of the 5 images from each category. the result of the query should have 6 rows with images with the different categories, eg: image_id / category_id / description 2 / a / test 7 / b / test 12 / c / test 29 / d / test 53 / e / test 77 / f / test is it possible to do this?
ORDER BY RAND()
RedTable(name TEXT,newness INT) I want to select random records but at the same time select those with the highest 'newness' value. So if the highest newness value in the table is 5 and ten records have that value I want to select a random one from those ten, but if all the records have the same 'newness' I want a completely random one. Can I do this: "SELECT name FROM RedTable ORDER BY newness,RAND() LIMIT 1" I don't know if that will really work. It's important that I maintain randomness so I really need to be sure.
Rand() Not Working In Php?
I'm trying to get the rand from the mysql. When I use the MySQL Front software (SELECT * FROM keyword ORDER BY RAND() LIMIT 2, it gives me random which is working fine. But when I use the php and it doesn't give me random. What wrong with php? $query = "SELECT * FROM keyword ORDER BY rand() LIMIT 2"; $result = mysql_query($query) or die("Query failed : " . mysql_error()); echo "<PRE>"; while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { print_r($line); } echo "</pre>"; mysql_free_result($result);
Rand() Question
hey guys i need mutiple images on one page in random use. i have this $QQQ1 = mysql_query("SELECT * FROM supps AS t1 INNER JOIN ( SELECT ROUND(RAND() * (SELECT MAX(id) FROM supps)) AS id ) AS t2 ON t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1; ");
Optimize Rand() On A Big Database
I am using RAND() and I know it is killing my MySQL performance. My database is currently at 200K rows and growing quickly. Here is a general mysql SELECT that is looped about ten times depending on the page. PHP if($drange == "thisweek") { $subquery = "SELECT articles.title, articles.link, articles.date, site.site_name FROM articles,feeds,site WHERE articles.article_id != $articleid AND (week(articles.date)=week(now()) AND year(articles.date)=year(now())) AND articles.feed_id=feeds.feed_id AND feeds.sub_id=$subid AND feeds.site_id = site.site_id ORDER BY RAND() LIMIT 3"; } I am trying to optimize this to NOT use RAND(). For this query, it is looking at all the latest additions over the past week. I have thought about running this query only selecting the IDs of the rows, then randomly selecting 3 numbers out of the selection and running the query again just to select the rows. But would that be faster?
RAND() With Weighted Probabilities
I don't really think this is possible, but I've had a few other posts where I've said just that, and have been pleasantly surprised in the end... I'll be running some ads on the side of my site in an attempt to earn nominal amounts of money--ads in the plural, referring to multiple sources (e.g. Google AdSense, AdBrite, and a couple others). However, I will only be displaying ads from one source at a time. This is done rather easily, by storing the Ads HTML in a database which I've named advertisements, selecting them like this: SELECT ad_html FROM advertisements ORDER BY RAND() LIMIT 1However, when I do that, each ad has essentially the same probability of appearing as the next. For instance, if I have a total of 5 ads saved in the database, each one has a 1/5-chance of appearing. I would like to give "weights" to each of the ads, so to speak, meaning that particular ads should show up more often than others. The easy way to do this, of course, is to just store duplicate rows in the database, thereby naturally increasing the chances of certain ads being selected. But that seems messy, both in theory, and because it takes up more space. I'm wondering if there's any way I can add another column to my advertisements table, call it "ad_weight" (or something like that), that would be a real valued number from 0 to 1, indicating the probability that the particular row it describes will be selected. I have absolutely no idea how to do this, nor do I know if this is really possible, so any sort of help is much appreciated! A pure SQL-query solution is definitely preferred (if it exists), although I'd be willing to do something equivalent in PHP, if that's what it takes.
ORDER BY RAND() Same Every Time
I'm trying to get a random record from a table, but the overwhelmingly recommended method - "SELECT * FROM table ORDER BY RAND() LIMIT 1" - gives me the same record every time. When I try the query without the LIMIT clause, I can see that the records are indeed in an order that cannot be explained as anything but random (i.e. not the order I entered them, and not the order of the primary key), but when I run the query again they repeatedly show up in the same "random" order. Is there something I need to do to seed it so that my results change? I see no mention of this anywhere - everyone seems to act as if this query will simply work.
Group By And Order By Rand()
I have the following data in a table: | art_id | artist_id | category_id | title | | 1 | 1 | 1 |A Golden Day For the Guardians of the Earth| | 3 | 1 | 1 |Blizzard Along Bear Tooth Range| | 9 | 1 | 2 |Spring of the Rainbow Warriors| | 8 | 2 | 5 |Evening at Rock Island| | 5 | 2 | 2 |Snowy March Evening| | 2 | 2 | 7 |Afternoon Missouri River| | 4 | 3 | 1 |Pelican King| | 6 | 4 | 7 |Moose Cabin| I am trying to select a random art_id , but I only want 1 id per category. I tried: select art_id,artist_id,category_id, title from art group by category_id order by rand(); but it always gives the same art_ids, just in a different order, so I always get art_id 1 for category 1, never id 3 or 4.
Group By / Rand Problem
I try this sql, but it don't work :( Have you a solution ? ---------------------------------------------------------------------- SELECT galleries.id_gallerie as lid , ( SELECT id_contenu FROM contenus WHERE id_gallerie=lid ORDER BY RAND() LIMIT 0,1 ) as lid_contenu FROM galleries WHERE galleries.home=1 AND galleries.online=1 ------------------------------------------------------------------------------
Order By Rand() Problem
this query works fine in phpmyadmin: SELECT name, id FROM `raffaello_ties` ORDER BY rand( ) LIMIT 0 , 30 when I try to do the same in php, I get an error, although I don't know why. here's the error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by rand() limit 0, 30' at line 1.
Faster RAND() Query
I need to select from my db table ONE (1) random row, just one. So, I am doing this: SELECT categories.cat_name, subcategories.sub_name, map.faq FROM categories, subcategories, map WHERE categories.cat_id=map.cat AND subcategories.sub_id=map.sub ORDER BY RAND() LIMIT 1" categories=10 rows subcategories=20 rows map=100 rows
What Does LIMIT Do?
in MySQL, when I do LIMIT x, how does the database work? 1) Does it pull everything relevant, then only takes the x starting from 0 or. 2) Does it only pull the first x amount out and stops?
I Need A Help About LIMIT
i have 10 rows in a table i want to select the last 3 rows only i tried: "select * from table1 order by id desc limit 0,3" it selected rows 10, 9 & 8 but i want it ordered like 8, 9 & 10, what should i do?? i also tried: "select * from table1 order by id asc limit 0,3" but it only selects the first 3 rows it worked when i used subselect: select * from (select * from table1 order by id desc limit 0,3) as test order by test.id asc but my host is using mysql 3.23, which does not accept subselecting.. i also tried: "select * from table1 order by id asc limit count(*)-2,3" but it only gave me an error msg i even tried: "select * from table1 order by id asc limit 3,-1" but same: error
Limit On A Key
I was wondering if there was a way, in MySQL, in a table definition, to set a "limit" on how big a key can get with auto_increment that's not based on data type. More specifically, I'd like the key to be limited to 0-63 or 1-64 (the first prefered, but I know auto_increment starts at 1). The smallest integer data type is 255 values or 127 signed. Basically, I want to be able to store these keys as a flag in a BIGINT which is limited to 64 possible flags. (If there are bigger precise data types, I'd love to know.) I sifted thru the MySQL manual and found nothing. Other solutions are welcome. The requirement is to be able to store 0-* keys in a single column to be compared to a list of keys from other sources, and I think being able to use bitwise comparison is much more efficient than doing a script side cross-reference. I could have the script manage the key, but if MySQL could do it for me.
LIMIT
how to display the remaining result using LIMIT function ex. SELECT * FROM tbl1 WHERE title = 'field1' LIMIT 10 now the query will display only 10 retrieved records but what if found 20 records, that is my problem... how to display it.
LIMIT
When I execute the following query without the LIMIT clause, it executes under 1 second. When I add the LIMIT clause, it takes 10 seconds to complete. The client table contains 100,000 records. What is going on? SELECT * FROM clients WHERE rep_id = 1 AND last_name LIKE '%au%' ORDER BY first_name DESC LIMIT 0 , 10
Limit?
i saw the following in a mysql query: $stmt = "SELECT id, first_name, last_name FROM persons LIMIT 0, 3"; in that code, what does "LIMIT" mean, and what does it do?
Limit Sql 4.1.22
error from query: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' SELECT * FROM news WHERE news.id NOT IN (SELECT news.id FROM news ORDER BY date DESC LIMIT 15) If limit is not supported how would i archive this?
Using LIMIT
If I'm querying a table where I know it's going to be a unique answer should I put LIMIT 0,1 . My thinking being that if the record I'm looking for is the 3rd of 250,000 then using LIMIT will stop the query after it's found the record instead of going through the other 249,997 records. Is this right? $sql = "SELECT * FROM db_sector WHERE sector_x = '" . $x . "' AND sector_y = '" . $y . "' LIMIT 1"; $result = mysql_query($sql); $sector_id = mysql_result($result, "0", "db_sector.sector_id");
Limit
I'm running a query to get a list of students and the class they belong to. Is there any way I can run a single query to return only 4 students from every class if there are more than 4 in some of them? The students have student ids, so I would want the first 4 student ids.
Using Limit
I am trying to use Limit in a subquery but receive an error Is there another way to archive the same end result without Limit? SQL: SELECT * FROM news WHERE news.id NOT IN (SELECT news.id FROM news ORDER BY date DESC LIMIT 15) Error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
Limit
I want to make a top 3 lists from a table with a query like this SELECT points FROM table ORDER BY points LIMIT 0,3 This will of course return a list of the 3 lowest values of points in the table, lets say 1,2,3. Now to my problem... If several records in the table has 3 points I want to have all those records returned, lets say 1,2,3,3,3 In MS Access this can be solved by SELECT TOP 3 from.......
LIMIT
If I have a sql statement like this: Code: SELECT id FROM `asset` WHERE field = 'value' LIMIT 20 , 40 Is it possible to know how many results there would've been if I hadn't used the LIMIT? Or will I need to do the above query and just count the results first, then apply the LIMIT and run the query again to get my limited set of results?
Limit
Could some explain the "LIMIT" function to me? For example, I have the following Code: $sql = "SELECT name, bio, headShotFile FROM artists WHERE id='$id' LIMIT 0,1"; I bought a web site from a friend and now I'm picking apart the code and learning. I've done some surfing, but couldn't find a clear explanation, only uses of it. Yet I want to make sure I fully understand it.
Limit
I am in the process of creating a download script, I am using a code to determine if someone is granted access or not to download the file. I want to set it up so they can only download, say, 250 MB a month worth of renders. And then, have the option for them to purchase an upgrade to their download limit, which will increase the limit for that user only. I want to use my vBulletin memberlist for this, so I already have a database with the users. I am a noob with MySQL though, and I am completely stumped. I tried this with PHP but failed?
Using LIMIT 0,1
Let's say I do a check for a duplicate email address in a database by using SELECT COUNT(*) FROM emails WHERE email='' .$email. '' Will it be faster (as in end sooner) if LIMIT 0,1 is stuck at the end of that query, or does it not matter?
LIMIT Problem
I am having a problem with this mySQL query: SELECT * FROM my_table LIMIT 4,10 When I do this query in phpMyAdmin, it works like it should - returning 10 records from row 4. When it is in my code, it always returns 10 records from row 0. Why would the exact same query work in phpMyAdmin but not in my code?
Joins And Limit Used Together
I have a query, which reads all information from tables. select se.section_name,pr.*,ph.code from prices pr left join pharmacy ph on pr.pharmacy_id=ph.id left join sections se on pr.section_id=se.id where medication_id=7 order by se.order_id asc, cast(`dosage` as SIGNED) asc, cast(`total_price` as SIGNED) asc result can be viewed here: http://www.foreign-drugstores-online...oduct.php?id=7 I need to read only top 5 (total price) for each `dosage` somekind of: group by `dosage` order by total_price desc limit 5 per group
GroupBy And Limit
I have the following query which I would like to sort by a dateToPull field that is stored in a table called date. SELECT story.title, story.article, story.deptId, story.authorId, author.author FROM story, author WHERE homePage = 1 AND story.authorId = author.id My goal for the query is to retrieve all the stories that have not yet reached their respective date to be pulled from the page, after retrieving these stories I then will limit only three to be displayed. My attempts to use and IF statement did not work but I have never used GroupBy. I plan to use the LIMIT function at the end of the statement to display only three stories. The above query works great, except it does not yet have the function to sort by date. Where do I begin to approach this problem from?
Issue With LIMIT
PHP SELECT item.itemId, item.itemName, 2 * SUM(bans.bansForAgainst) - COUNT(bans.bansForAgainst) AS count, bans.bansForAgainst, bans.bansItemId FROM item, bans WHERE item.itemId = bans.bansItemId GROUP BY bans.bansItemId ORDER BY item.itemId DESC LIMIT 0,5 But it's not limiting the output...at all.
LIMIT Query
I have a query that returns all the room numbers in a hotel. What would be the proper syntax for a query to return one instance of each room. e.g. i want 1 2 3 4, etc. instead of 1 1 1 1 1 2 2 2 2 2 3 3 3 3 3, etc.
Using LIKE And LIMIT In A Query
Is it possible to use LIKE and LIMIT in a query? I am trying to get search results and limit them. Ive tried different ways but it doesn't seem to work. I have looked up syntax for both LIKE and LIMIT but cannot find one that includes both. $query = 'SELECT * FROM table WHERE rows LIKE "%$q%" AND LIMIT 0, 10'; $query = 'SELECT * FROM table WHERE rows LIKE "%$q%", LIMIT 0, 10'; $query = 'SELECT * FROM table WHERE rows LIKE "%$q%" LIMIT 0, 10';
Primary Key Limit
I need to have a long primary key (it sounds strange but...). Mysql say that it not possible to have a primary key longer than 500 chars (i need 520 chars!!!). Is it possible to change this limit by set up an option?
LIMIT Statement
I'm kind of new to MySQL but am familiar with other DBs. Currently I'm working with a MySQL4 database and I have a very specific problem which could be easily solved via views. Problem: I have as follows - tbl_cat -id_cat -txt_name ... tbl_prop -id_prop -id_cat (fk) -txt_desc -txt_type -txt_default .... tbl_product id_prod txt_ref_name ... tbl_prod_prop id_prod_prop id_prop txt_hash OK what is all of this? it's a dynamic product for a shop. How does it work conceptually? We create a product category(tbl_cat) and add properties to it (tbl_prop) such as name, picture, html text, etc... Finally when we create a product, we determine it's category and fill in each of the properties. My original idea was to use a view to generate the listing of the products and properties, but mysql4 doesn't have views... What would be the best aproach? I thought of creating a cache table, where when a product is updated or inserted, I run a script that creates updates a record of a temporary dinamic table. Soething like a false view, updated on demand. Is that as stupid as it sounds?
'Access LIMIT'
Is there a way to specifiy a password with mysqldump? I would like to add a few mysqldump commands to a cron but can't seem to figure out a way to schedual them without direct user input of the password mysqldump -h mysql.yourdomain.com -u user -p dbname > /home/user/mybackup.sql
LIMIT Problem.
I am having problems with the LIMIT parameter of a MySQL query. The query is displayed below: $imgtype = $_GET['imgtype']; $cnt = $_GET['cnt']; if (!isset($cnt)) { $cnt = 0; } //select proper row in table based on cnt. $sql = "SELECT * FROM `test` WHERE `imgtype` = '$imgtype'"; $sql .= " LIMIT $cnt, 1"; $result = mysql_query($sql); $row = mysql_fetch_assoc($result); $imgid = $row['ImgId']; $imgname = $row['ImgName']; The table is populated by Image names, types, and location. As you can see I am grabing all the records by $imgtype, then selecting one at a time based on the $cnt. Every time the page is reloaded the $cnt increments by one, so it should select the next row in the table based on the $imgtype and $cnt. My problem is that even though I should be selecting only the records with the correct $imgtype. Its not, its selecting all the records in the table, even the ones with a different $imgtype.
Field With No Limit
Is there a field in mysql which there is no limit to how many characters the field can hold?
Getting Other Data After Using LIMIT?
I have a table with a few hundreds records of products information. I need to retrieve the top 10 records based on the sum of the products quantity. My query is as below: Select *, sum(qty) as SUMQTY ,sum(amount) as SUMAMOUNT from tdetail group by barcode order by SUMQTY desc limit 10; This query gave me 10 records of products(barcode). My problem is that i need to have another 2 fields for the percentage calculation( eg.sum(qty) of product N/total qty , sum(amount)of product N/total amount.), but i dont know how to get the total qty and total amount. How do i do that in one query?
|
TRACKER
