Hi, How can I select the 100 largest rows in a table based on a column 'score'? I can find the largest score in the 'score' column with: SELECT max(score) FROM mTable And then obtain that row(s): SELECT * FROM mTable WHERE score=largestScore But how would I wrap this up and obtain the following 99 lower scored rows? Thanks. ...
Given this sample dataset: item ---- item_id item_name item_added 1 Apple <date_time> 2 Banana <date_time> user ---- user_id user_name 1 Alice 2 Bob 3 Carol rating ------ rating_id item_id user_id rating_value 1 1 1 3 2 1 2 4 3 1 ...
All, I'm using this as the conditional to my select statement. WHERE RecDate >= '%s' GROUP BY DATE(RecDate), HOUR(RecDate), MINUTE(RecDate), SECOND(RecDate) - (Second(RecDate) \% %d) LIMIT %d Currently this query is taking a bit too long. What I'm trying to do here is gather X amount of rows after a certain date. I also don't want e...
I have an Access database currently. There is a backend, and there are multiple users who use this database with their own front end. The size of the data is very small; however there are several forms that are constantly in use. I need to migrate the data to MySQL. Should I continue to use an Access front end or program my own c# front...
I have two tables: attractions and cities. Attractions contains a column called city, which is a reference to the id in the cities table. I want to form a MySQL statement that will show me which cities have the most attractions. I know I can do: SELECT COUNT(*) as `number` FROM `attractions` WHERE `city` = XX ...to get a count o...
I have a table in MySQL that have a few columns that have default values specified, but when I try to insert a row, (not specifying values for those default columns), it throws an error saying I cannot insert NULL values. I am no MySQL expert, but I would expect you to be able to insert a row without having to specify the values for the...
I develop application run in Websphere work manager. work manager is used to run thread in the webpshere applications erver. Every 5 minutes my thread try to get some data from MySQL database from the different host from the application server machine. When the Host of MySql database turned off, The work manager always try to connect t...
I am using PHP to interact with a MySQL database, and I was wondering if querying MySQL with a "SELECT * FROM..." is more or less efficient than a "SELECT id FROM...". ...
Identity(1,1) equalent function in MySql? ...
SELECT title, title REGEXP 'apple' as is_fruit FROM mytable; TO: SELECT title, title REGEXP 'apple' or orange...or grapes...as is_fruit... Basically, how do I do an "OR" for REGEXP? ...
One of my tables has crashed, message logged in error log, and the following works as expected. mysqlcheck mydbname -a tbl_name error : Table './mydbname/tbl_name' is marked as crashed and last (automatic?) repair failed But when I use the mysqlcheck command without the -a parameter, I do not get this table. mysqlcheck mydbname ...
Hello friends, I wanna create a poll system with possibilty to have many answers on my website but im thinking whats the best way. Im not so pro. Im thinking this schema: polls ------------------------------------- id | question | start_time | end_time poll_answers --------------------- id | poll_id | answer poll_votes -------------...
I have this table CREATE TABLE `zipcodes2` ( `ID` int(11) NOT NULL auto_increment, `zipcode` int(6) NOT NULL default '0', `State` char(3) NOT NULL default '', `zip_name` varchar(255) NOT NULL default '', `CityAliasName` varchar(255) NOT NULL default '', `latitude` float(26,7) NOT NULL default '0.0000000', `longitude` floa...
I recently have began using prepared statements again in a web application, and I know that it is discouraged to use prepared statements for all the transactions. What I do not know is when it is best to use prepared statements or not. I have read of when to use and not use them, but none of the examples really tell best practice of usi...
Hi, I am here to design a relational database schema that models polling and users. Each category can have one or many questions. Each user can participate only once at each category, and can poll (or not) exactly once on each question. Each poll is yes, no, or abstain (no vote). I have designed my schema with four tables: users, [u...
I'm struggling to get week of year (from 1 to 52) in mysql query using friday as starting date. My friend said just add current date with number (because friday is 5 and sunday as default starting date is 0) 5 so that we can know week of year of given date. What function will satisfy this problem? as for given that if value is 2009-12-2...
Hi, I'm having problems using conditional ORDER BYs. I want to do something like this SELECT promotion_expires,created_at FROM `notes` ORDER BY CASE WHEN (promotion_expires > NOW()) THEN 'promotion_expires DESC,created_at DESC' ELSE 'created_at DESC' END; ie. the result should first have rows where promotion_expires > NOW() o...
Hello. Recently, I learn to develop a rails application. Now, I have a problem. I want to change into Production Mode. But I don't want to copy my data in developement database manually. How should I do these easily? I use mysql and Mac os and rails 3 beta. Tks. ...
I want to create a friends system like facebook, well quite weird than that. When a user adds someone, the someone will be notified, if agree then they'll be mutual friends(in this case, meaning both parties are friends, literally) or if the someone leaves the request unresponded, they'll become fan of the someone. The someone reject the...
Hey all, i am having a weird problem with trying to update a record in my mySQL 5 database using VB6. This is my code when i log in: connDB Set rst = New ADODB.Recordset strSQL = "SELECT id, fName, lName, theCode, theDate, clockin FROM clockinout WHERE theCode = '" & theUsersUniqueID & "' AND theDate = '" & Format(Now, "YYYY/MM/DD") &...