I have a table with several integers as primary key. One of them is a counter. I need to remove the counter from the key. Removing counter from the key will make many rows duplicates (with different counter values but all other key elements the same). I need a query that will delete all duplicates and leave only the row with the highes...
I'm trying to create a faster query, right now i have large databases. My table sizes are 5 col, 530k rows, and 300 col, 4k rows (sadly i have 0 control over architecture, otherwise I wouldn't be having this silly problem with a poor db). SELECT cast( table2.foo_1 AS datetime ) as date, table1.*, table2.foo_2, foo_3, foo_4, fo...
In my DB i store a center point, along with a radius (in meters). I'm looking to pass in a lat/lng, and then have the mysql values i've stored create a circle to tell me if my point i passed in is within that circle. Is there something that would allow me to do this, similar to the haversine forumla (which would assume that my point w...
SELECT webcal_entry.cal_id, webcal_entry.cal_name , webcal_entry.cal_priority, webcal_entry.cal_date , webcal_entry.cal_time , webcal_entry_user.cal_status, webcal_entry.cal_create_by , webcal_entry.cal_access, webcal_entry.cal_duration , webcal_entry.cal_description , webcal_entry_user.cal_category FROM webcal_entry, webcal_entry_user ...
data: id1，id2 1 3 1 8 1 10 1 11 2 3 2 10 2 11 3 2 3 18 3 20 4 3 4 8 5 3 5 10 5 11 5 40 5 45 5 50 6 1 6 59 6 70 I won't get all id1 with id2 ＝ 3，10，11. For example, id1=4 only with id2=3, should not return. The results should be id1 1 2 5 ...
The PROBLEM I have a query like this: select a.id from a join b on ( a.id = b.my_a ) join .... where ( /* complex and expensive conditional */ ) AND (( /* conditional #1 */ ) OR ( /* conditional #2 */ ) OR ( /* conditional #3 */)) I would like to have the query return something like: select a.id, co...
I'm pretty green on mysql and I need some tips on cleaning up a query. It is used in several variations through out a site. Its got some subquerys derived tables and fun going on. Heres the query: # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SELECT * FROM ( SELECT products . *, categories.category_name AS category, ...
Hi! I wonder if it is possible to use FK's in Mysql (InnoDB) for inverse lookup. The reason - I want to read an XML-like structure from the db (using one table per "layer"), but be able to do this dynamically. I want to be able to update the xml structure by adding a new table and setting a FK constraint. To clarify, say we have a tab...
Mysql: Table_A ------------ id name 1 Name1 2 Name2 Table_B ------------ a_id type value 1 width 100 1 height 100 1 color red 2 width 50 2 height 80 It's unknown how many type values exist in Table_B. how to get result as: id name width height color 1 Name1 100 100 red 2 Name2 50 80 null ...
I have a database of users and a database of cards. The users see the cards repeatedly. I kept a record of every time the users saw the cards and how long they looked at the card. Now that I've got that data, I want to analyze it by considering the first time that each user looked at each card. I got a list of the average msToAnswer ...
Is this possible? Before a row is inserted into tableA, I need to delete any rows in tableA containing duplicate data.The trigger below does not generate an error but doesn't work either. CREATE TRIGGER remove_old_user BEFORE INSERT ON userStatus FOR EACH ROW DELETE FROM userStatus WHERE username = NEW.username Any ideas? ...
I'm looking to create a sql statement that will update a large set of data. What I have is a table like id, transid, amount, narative1, narative 2, total, active 1 1234 23.2 NULL NULL NULL 1 2 1234 120.33 NULL NULL NULL 1 3 1235 98.00 NULL ...
I have a tab;e set up as follows id origin destination carrier_id so typical row could be, 100: London Manchester 366 Now each route goes both ways, so there shouldn't be a row like this 233: Manchester London 366 since that's essentially the same route (for my purposes anyway) Unfortunately though, i have wound up w...
I need to extract the following fields into a new table, any ideas if I can do this exclusively with a query or if I need to use PHP as well. CURRENT TABLE STRUCTURE USERID USEREXPERINCE 1 a:4:{i:0;s:20:"business development";i:1;s:6:"design";i:2;s:9:"marketing";i:3;s:15:"press relations";} REQUIRED TABLE STRUCTURE USERID ...
Hello everybody, I'm trying to write a valid mysql statement that would allow me to update multiple columns in one record with values provided as python variables. My statement would look like this: db = MySQLdb.connect(host="localhost", user="user", passwd="password", db="dbname") cursor = db.cursor() sql_update = "UPDATE table_name ...
I am developing a video website (PHP - MYSQL), just like youtube, in which I want to provide the functionality of Next video and Previous video. Let's say I am currently on videoId: 234 so Next and Previous video links will point to videoId: 233 and 235 respecively. My table structure is as follows: videoId videoName videoURL ...
Hi. I am using MySQL for my database. I have a requirement to store a list of users in the system. The user will have both first name and last name. I cannot have the first name and second name as the primary key. So I need a indexing key. But I do not want to enter the index value every time to add a new row. I want the system to ha...
My data looks like the following: id|category|insertdate|title.... -------------------------------- 1|1|123|test 1 2|1|124|test 2 3|1|125|test 3 4|2|102|test 4 5|2|103|test 5 6|2|104|test 6 What I try to accomplish is get the latest 2 entries per category (as in order by insertdate DESC), so the result should be: id|.... ---- 3|......
Consider the following database tables: Table "messages" with 13,000,000 rows (one row per message). Table "users" with 3,000,000 rows (one row per user). The following query is used to fetch a bunch of messages and the corresponding users: SELECT messages.id, messages.message, users.id, users.username FROM messages INNER JOIN users...
I have two databases: Transactions and Customer List I want to populate the customer list database with the customer information that is in the transactions database. I do not wish to transfer ALL of the information from the transactions database because the Customer List database has a different structure. How can I transfer these r...