How To Write Group By Query
i create table item-info. i want to view my data in the following way. i write query use group by but i want name show only one time no repeatation name next line.
item-info table:
id name item price
1 A XX 15
2 B XX 16
3 A YY 17
4 C YY 20
5 B ZZ 50
6 A ZZ 40
View my data in the following way:
name item price
A XX 15
YY 17
ZZ 40
B XX 16
ZZ 50
C ZZ 20
View Complete Forum Thread with Replies
Related Forum Messages:
Best Way To Write This Query
Query 1: obtains results in boolean mode from products table based on keywords Query 2: Would like to grab all manufacturers names from manufacturers table based on mf_id in products table to then create a brand filter. What is the best way to do this in the most efficient query possible. I was thinking of building a php array from query 1 of mf id's and the putting them in query 2 and separating them using OR operators. any ideas? here is the query fyi: PHP $sql = "SELECT products_id as prodid, products_name as name, manufacturers_id as manufacturers_id, products_price as price, products_msrp as msrp, products_date_added as pda, products_status as status, products_sku as sku, products_weight as weight, categories_id as cat_id, " .boolean_sql_select( boolean_inclusive_atoms($search_string), $fulltext_key)." as relevance " ."FROM $table_name " ."WHERE " .boolean_sql_where($search_string,$fulltext_key)." " ."HAVING relevance>0 "; if (isset($HTTP_GET_VARS['sort'])) { if ($_GET['sort']=="brand") { $sql.="ORDER BY products_name "; } elseif ($_GET['sort']=="size") { $sql.="ORDER BY products_weight "; } elseif ($_GET['sort']=="price_low") { $sql.="ORDER BY products_price ASC "; } elseif ($_GET['sort']=="price_high") { $sql.="ORDER BY products_price DESC "; } elseif ($_GET['sort']=="fav") { $sql.="ORDER BY products_name "; } } else { $sql.="ORDER BY relevance DESC "; }
View Replies !
To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 12004-01-10110 22004-01-20120 32004-01-20270 42004-01-10280 52004-01-15110 62004-01-25310 Output : RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 22004-01-20120 32004-01-20270 62004-01-25310 Now I want to use a single SQL query to find a result where there exist one record for each unique "othertableid" where the record selected for the "othertableid" should be the recent one with regard to "EffectiveDate". That is from the above records, I want to select Records with "RecordId" = 2 and 3 because they are the recent one for "othertableid" = 1 and 2 respectively. Please be sure that I want to retrive all fields including "RecordId". The result should not depend on any other fields but "EffectiveDate" only. I am using MySQL 4.0.12 and it does not support "SUBQUERIES" which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any "SUBQUERY" SQL statement can be written with proper "JOINS".
View Replies !
How To Write Query To Compare Tables
I give in. I can't figure it out, and I know it's one of those things that once I see it I'll think, "OF COURSE!". I have 2 tables, same DB. Table 1 is named Policies Table 2 is named Assignments Both contain a PolicyID field. I want to find out which PolicyID entries are ONLY in Policies, and not in Assignments. So if "Select PolicyID from Assignments" returned the following: 1, 2, 3, 4, 5, 6 And "Select PolicyID from Policies" returned: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 What do I use to return this? 7, 8, 9, 10 Any nudge, help, clue, etc. would be appreciated. I've looked thru examples of Outer Joins, Inner Joins, Lefts, Rights, Unions, Intersects, etc. and can't figure out which to use, all for what seems to be a simple operation!
View Replies !
Trying To Write A Two-part Fast Query
The situation is this: there are two tables, one called "objects" (fields: object_id[int] and object_name) and properties (fields: object_id[int] and property_name and score[int]). Objects have one or more properties. In short, what these 2 queries need to ask are "what objects could an unknown object be, given what properties I know?" and "what property should I measure next to most narrow down the possible objects?" The objective of the first query is to return an object who has all of the given properties scored above or below the given value. For example, I might look for the object_id of an object who has the property "green" scored above 3, the property "hot" scored below 2, and the property "bright" scored above 0. It might have other properties, too, but it must have these 3. And then, for the second part, I need a query that will select properties which are NOT in this set of given properties but are relevant to the returned set of objects. In this part, I'm not concerned with the property's score - just that it is relevant to the objects. In other words, this query might tell me that the property "hard" is relevant to 3/4 objects (even though one of them had a very low score for the hard property, another had a very high one, etc). Basically I want to know which new property, once I can determine the score of this new property, will help narrow down the object-set the most, so that I can collect data for the most relevant property to narrow down the object set.
View Replies !
Write Tricky Mysql Query (related Tables)
member id(int, pk) name(varchar) profile_pic(varchar) other_fields books id(int, pk) cat_id(int) name comment id(int, pk) comment_type(enum: books, more_type) comment_id(books id) comment Sample data of comment id comment_type comment_id comment ------------------------------------- 1 member 1 comment about first member 2 books 1 this book is really nice 3 books 2 horror books are instesting 4 member 5 paype is the sailor is now a member now i need a SQL Query to "select all books information having cat_id=1 along with user inputed comments about those books".
View Replies !
How To Write Query To Select The Max(version) For Each Unique File_name Record?
I am a MySQL newbie trying to write a query that selects file_name records possessing the highest numbered version for that unique file_name. I show sample data and two trial queries below. Logically I want to use max(version) as a constraint in a Where Clause. However, the max() function is not allowed directly in a where clause. I have contemplated a second table to track the max version for each file name. I would like to structure the data in an efficient manner for query performance when the data set grows to many thousands of unique file_name records with many hundreds of versions each........
View Replies !
Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables. What is the best approach one of these or another? After generating a candidate 'unique string' the two strategies that came to mind are: 1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or 2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again. I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.
View Replies !
Sum()/Group By Query Help
I have tried searching for a solution to this, but I can't seem to find a good set of keywords, so I'm hoping some kind soul will answer it for me. I'm not sure it is even possible, but it certainly seems as though it should be....
View Replies !
Group By Query
I have the following query that is dynamically generated: SELECT did, SUM(weight/3) as didweight FROM documentkeywords WHERE word = 'test1' OR word = 'test2' OR word = 'test3' OR word != 'test4' GROUP BY did ORDER BY didweight the idea is to select keywords and add their weights then devide by the number of keywords, the trouble is i wish to remove items from the result that have a word that is not supposed to be there (eg 'test4'). How can i do this?
View Replies !
Using GROUP BY In Query
I have a table with many fields and among them I have an index called `id` and a timestamp called `timestamp`.I want to be able to select rows according to my WHERE clause, but only access the rows with the greatest `timestamp` for each `id`. As if the table is really only composed of one instance of each value of `id`. So I am using the GROUP BY on `id` to limit the results to one row per unique id.Code: SELECT * FROM `person` WHERE `company_id` = 1 GROUP BY `id` ORDER BY `timestamp` DESC, `last_name`, `first_name` Which gives me one row per value of `id`, but I can't figure out how to get it to order by the latest timestamp in each grouping. The ORDER BY clause in the example doesn't work.I have tried approaches using DISTINCT and HAVING, but I can't seem to get it right.
View Replies !
'Group By' Query Help, Please
I'm having trouble with a query In using in conjunction with a Group By clause. SELECT ur.userID, r.referral, COUNT( r.referralID ) AS overall FROM referral AS r LEFT JOIN userreferral AS ur ON r.referralID = ur.referralID GROUP BY ur.userID LIMIT 0 , 30 The above query gets no more than 30 users, with each row representing a unique user. Each row has a count column that counts the number of "referrals" a particular user has, Group By is used so we only get one row per user. My problem is that say if a user has 10 referrals, the above query will return the first referral made to that user, My question is How can I make it so the above query returns the 10th referral (ie latest) referral made to that user?? I know theoretically all i need todo is reverse the referral column for each user so the last one is diaplyed first but I cannot work it out.
View Replies !
Query A Specific Day, And GROUP BY
The query below, counts how many records each agent has entered in the previous 7 days and groups them by agent... Then it also counts a total for all the agents for the previous 7 days as well, not grouped. How can i query a specific day of the week, say 4 days back, only count that days records and group them by agent?
View Replies !
DISTINCT/GROUP Query.
I have some serious issues with a DISTINCT Query. The table is for logging users. Let's say the table looks like this userID | average | timestamp 1 | 20 | 2008-01-16 00:00:00 1 | 35 | 2008-01-17 00:00:00 2 | 12 | 2008-01-18 00:00:00 1 | 16 | 2008-01-20 00:00:00 What i want to go get is the the DISTINCT userID (every user, but only one time) and i want to get the latest value of the avarage field (so sorted by timestamp desc). I tried many ways to get this but i can't seem to get it to work, DISTINCT is using all the fields i add, and if i GROUP i either have to say like, MAX/MIN and since there is nothing that makes the average increase/decrease by every new post in the database. My third try was to order by the timestamp, but i doesnt seem to work in all cases, only sometimes.
View Replies !
How To Group/merge Query
I'm trying to query from a specific column in the table. But there will be repeated values. I wonder if there's any function I can use to "merge" the same ones and end up just a list of all the non-repeating values. Don't know if I make sense here... hope the following helps: Column A -------- apple pear orange apple orange orange cranberry and I want a query that gives me "apple, pear, orange, cranberry" only.
View Replies !
Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories. What I want the query to return is this: Date | ShopperID | VATable Amount | Non VATable Amount| 2006-3-1 | 802135 | 146.95 | 54.00 | Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above. I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product: +------------+----------+-----------------+---------------------+ | Date | ShopperID| VATable Amount | Non VATable Amount | +------------+----------+------------+---------------+ | 2006-09-04 | 805284 | 0 | 64.00 | | 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a | 2006-09-04 | 805287 | 3.25 | 0 | customer has | 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products | 2006-09-04 | 805287 | 4.95 | 0 | - I need total | 2006-09-04 | 805327 | 0 | 53.95 | under each | 2006-09-04 | 805335 | 0 | 58.95 | ShopperID | 2006-09-04 | 805414 | 0 | 64.95 | | 2006-09-04 | 805414 | 3.25 | 0 | | 2006-09-04 | 805414 | 0 | 64.00 | | 2006-09-04 | 805414 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.95 | | 2006-09-04 | 805423 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.00 | | 2006-09-04 | 805423 | 3.25 | 0 | +------------+-----------+----------------+--------------------+ Heres the query currently getting these results (without the GROUP BY ShopperID). SELECT Date,orders.ShopperID, CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag' THEN items_ordered.price -- If it is an accessory add VAT ELSE 0 END AS 'VATable Amount', CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?(' THEN items_ordered.price -- If size 1 - 6 add price to other column WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?(' THEN items_ordered.price -- If size kids 12/13 add price to other column ELSE 0 END AS 'Non VATable Amount' FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= ��-1-30') AND (Date <= ��-9-31');
View Replies !
Problem With Multiple Table Query Using Group By
I am running MySQL version 5.0.27-standard-log SELECT COUNT( rant_rants.* ) FROM rant_rants, rant_users GROUP BY rant_rants.userid LIMIT 0 , 15 Why won't this work? Foreach each rant_rants.userid I want the total number of rows in rant_rants with that userid. I have the second table in there because I am going to add more to this query once I figure this out. Any ideas?
View Replies !
How To Create Complex Query With Group By And Count
I have a table usertags: usertags ------------ - usertagid - usertagsiteid - usertagtext I want to select the usertagtext and count of usertagtext grouped by usertagtext. I only want it to show usertagtext that also have a match in the usertagsiteid to a given value for usertagtext. Not sure if that makes sense so here is an example: ......
View Replies !
Count And Group By Month In 1 Query For A Date Range?
I have a table that has employees work history on it. Basically the only thing I am worried about is the start and end date. My boss wants a report that charts out the quantity of workers for the past 12 months group by the month. For example Employee--Start Date--End Date Charles-----2008-6-3---2009-2-1 Vicky-------2009-1-1---Present So in this case there would be a quantity of 1 from June 08 to December 08. Then their would be a quantity of 2 from Jan 09 to Feb 09. And Vicky would also be counted for March since she is still working. I can do this by creating PHP functions and putting it all together, but I was just wondering if there was a way to do this all in 1 query.
View Replies !
Full Yearly Weekly Group By Group Report
I have table called tblTransactions: TblTransactions Group | Months | Year | Transaction A | 3 | 2007 | 45 B | 2 | 2007 | 89 A | 7 | 2007 | 50 A | 8 | 2007 | 34 B | 4 | 2007 | 55 A |12 | 2007 | 10 A | 1 | 2008 | 88 B | 3 | 2008 | 34 B | 5 | 2008 | 70 A | 5 | 2008 | 45 A | 9 | 2008 | 88 B | 7 | 2008 | 99 A |10 | 2008 | 77 A |11 | 2008 | 99 How is the T-SQL to make the result like this (the result will display all of the week (FULL), the target is I can compare week by week between years and group. A | 1 | 2007 | 0 B | 1 | 2007 | 0 A | 2 | 2007 | 0 B | 2 | 2007 | 89 A | 3 | 2007 | 45 B | 3 | 2007 | 0 A | 4 | 2007 | 0 B | 4 | 2007 | 55 A | 5 | 2007 | 0 B | 5 | 2007 | 0 A | 6 | 2007 | 0 B | 6 | 2007 | 0 A | 7 | 2007 | 50 B | 7 | 2007 | 0 A | 8 | 2007 | 34 B | 8 | 2007 | 0 A | 9 | 2007 | 0 B | 9 | 2007 | 0 A | 10| 2007 | 0 B | 10| 2007 | 0 A | 11| 2007 | 0 B | 11| 2007 | 0 A | 12| 2007 | 10 B | 12| 2007 | 0 A | 1 | 2008 | 88 B | 1 | 2008 | 0 A | 2 | 2008 | 0 B | 2 | 2008 | 0 A | 3 | 2008 | 0 B | 3 | 2008 | 34 A | 4 | 2008 | 0 B | 4 | 2008 | 0 A | 5 | 2008 | 45 B | 5 | 2008 | 70 A | 6 | 2008 | 0 B | 6 | 2008 | 0 A | 7 | 2008 | 0 B | 7 | 2008 | 99 A | 8 | 2008 | 0 B | 8 | 2008 | 0 A | 9 | 2008 | 88 B | 9 | 2008 | 0 A |10 | 2008 | 77 B |10 | 2008 | 0 A |11 | 2008 | 99 B |11 | 2008 | 0 A |12 | 2008 | 0 B |12 | 2008 | 0
View Replies !
Group By With Order BY, Or INSERT INTO (SELECT * GROUP BY)?
I seem to have a few duplicates entries in a very large database. My database has two identical tables. One is written to on a regular basis (every few seconds) and the other is used for queries, and updated from the write table on a daily basis. I'm trying to get rid of duplicate entries for the queries, and I thought the most effective way to do this would be to run the query as Expand|Select|Wrap|Line Numbers
View Replies !
Invalid Use Of Group Function (was "Baffled By Query Error")
trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2 AND SUM(c.agent_product_time) >= '500' GROUP BY c.account_id ORDER BY mins ERROR: #1111 - Invalid use of group function
View Replies !
GROUP BY With Order Inside Group
I have a problem when grouping records - I can't manipulate data inside group. For example, I have table `images` with fields `name` (name of image, not unique) and `dtadded` (date of image adding). Then, I need to get all images names with distinct names where each name must be latest added name. Sample: ------------------------------ id, name, dtadded ------------------------------ 1 name1 2007-10-15 00:00:00 2 name2 2007-10-15 00:00:00 3 name1 2007-10-16 00:00:00 ------------------------------ I need to receive 2 results (for each name) 3 - name1 - 2007-10-16 00:00:00 2 - name2 - 2007-10-15 00:00:00 If I use SQL code: SELECT images.id, images.dtadded, images.name, count(name) FROM images GROUP BY `name` ORDER BY dtadded DESC I get results 1 - name1 - 2007-10-15 00:00:00 2 - name2 - 2007-10-15 00:00:00 It groups records with first row in database, but I need last row in table for each name. Question: How can I order results in side group to get needed results as described above?
View Replies !
Cant Write Or Delete
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
View Replies !
How To Write A Trigger
we have an invoice table with fields ( invoice_id,date, item_id,item_name, price_per_item,quantity,cust_id) here (item_id ,item_name and price_per_item) are from table item_details. now i have to write a trigger to track the users who have changed the price_per_item column before insertion in invoice table.
View Replies !
Write Data To Row With Via C API
I have an app written in C that sends data to text files. The files receive 8 variables and look like this: May 19 20:42:09 2002 7 Services 3.23 4.34 0 None 2.31 Rather than writing data to a text file, I need that data to go to a MySQL 5.0.41 database row. I've already got the C API working and connecting to the "localhost" database, but now I need to write data with each transaction. Optimally, I'd like to send the data directly to the database row, but there doesn't seem to be an INSERT statement to do that. What's the best method to write the data to the database directly?
View Replies !
Can't Write To File
Using MySQL 4.1.11-max on OS X 10.3. Using command line. Can't successfully use "INTO OUTFILE". Getting the following: mysql> select * -> into outfile '/users/steves/desktop/test.txt' -> fields terminated by ',' -> from industry; ERROR 1 (HY000): Can't create/write to file '/users/steves/desktop/test.txt' (Errcode: 13) At the shell, I get this: steves2ndmac:~ steves$ perror 13 OS error code 13: Permission denied Newbie in both Unix shell and MySQL. Can anyone tell me how to set permissions for Unix User mysql?
View Replies !
How To Write Trigger
want to copy the table data from one host to another host why because its live data . i want to update one host to another host. ie i have one host 192.168.1.1 (db name is EXample table is live) i want to copy the live table values into another table which is located in different host ie(192.168.5.5 db name is Datadummy and table is livedummy). in triggers is it possible or not?
View Replies !
Cannot Write To Database
The result I am getting is: request "Could not execute SQL query" SELECT dt FROM webcalendar_events WHERE dt in ('2009-03-13') AND calendar_id=cum_wetheralhalls FOR UPDATE Unknown column 'cum_wetheralhalls' in 'where clause' The strange thing is that when the calendar_id is a numeric value, it works perfectly. Is there anything that I can do in MySQL to get this to work with text-characters also, or do I have to use numeric values for this? Is this something in the PHP script that is preventing it from saving to the database? In case you need to know the table row calendar_id is varchar(30).
View Replies !
Help Me Write This Join?
Hi there, been ages since I wrote any sql, and I'm a little stuck but I figure this is easy for you folks. I have this original query: $posts = $wpdb->get_results("SELECT ID, post_title FROM " . $wpdb->posts . " WHERE post_status='publish' ORDER BY post_date DESC"); And this gives a lists of all posts. However, I want to only grab posts by a certain category now, say category #9. This information is kept in an intersection table called wp_post2cat. The columns in that table are: rel_id post_id category_id category_id is what I want, and post_id is the key pointing back to the original post. Posts can have multiple categories, but I just want the one listing where it's category 9, which is audio. So pseudocode would be something like: SELECT all posts WHERE post_status equals "publish" and the category is equal to audio (9).
View Replies !
How To Write This In MySQL?
I Want to check if a certain value doesnt exist in a ceratain field in my table as a condition. "select id from employees inner join translog on employees.emp_id=translog.empid inner join outofofficedays on employees.emp_id=outofofficedays.emp_id inner join holidays where (here is my question: ��-04-02' doesnt exist in the column translog.vtransdate) and (��-04-02' not between holidays.H_START and H_END) order by translog.vTransDate,employees.emp_id limit 1"
View Replies !
How To Write This Select?
I'm having some troubles writing a select. I have the following tables: theater ------- - id - name movie ----- - id - name showtime --------- - id - theater - movie - from - to - schedule I would like to list all the theaters with all the available showtimes and movies. I tried to, but i didn't get the result i was expecting. Can somebody help?
View Replies !
How Do I Write This Trigger?
I’ve been searching all over for the answer to this one. If you have the answer, it would help me out a lot! Using MySQL triggers, I want to sync content between two tables; I have table “user” and table “users”, each have similar columns “last_name” “first_name” “user_id” …etc, and when a row gets written to table “users”, I wanted that record to be replicated and inserted to “user” (with all the same field info). How do I write this trigger? I know, it’s anti-normalization, but it will really help me out with testing one of our site's authentication.
View Replies !
C- Program To Write To Myi , Myd File
is it possible to create a myi, myd file for mysql using a C program. I am using Stata that is written in C API, and I want to write the data out to MYSQL database. Using ODBC and loading the data in MYSQL from STATA takes forever. Can anyone please tell me if I can create the myd files by using the data in memory thru C programming.
View Replies !
Using INSERT To Write To A Directory
I have a problem that I'm not sure if it can be done. I'm trying to use the MySQL C API to be able to use a normal sql insert statement that will send the data or file to a directory and NOT the database. I'm not sure if there is anything out there that will allow this, but I've searched everywhere, and I have found no solution.
View Replies !
Write Conflict Error
I have a database with access as front end and Mysql as back end. I am gettting the following 'Write Conflict' Error. when i am on my order form, This form does has a subform where i enter all the products that are ordered. Could any one let me know what could cause this problem. I am the only one using the database as its still in the build stage Code:
View Replies !
Cant Write Or Delete To Table
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
View Replies !
MySql Write Vs Read
Essentially I am faced with a design choice; have an oft run (modifiable) query run every time any user wants to run it, comparing criteria againt each and every user in the table and delviering list OR saving that list (user/user/) so the next time all that needs to be queried is the existence of a record in the table. The basic tradeoff; version 1 there are many simultaneous queries of the user db on complex search criteria; version 2 there are fewer but their are as many far more simple queries to a much larger table, not to mention many writes to same to keep the table current. I know this is pretty vague, but I was wondering if in general there is an area that mysql excels in vis a vis complex query smaller tables simple query larger tables. Question 2 is there anyway to guestiate the search/write times of a user log in to both query and update? #Records, fields, etc? I'd like to keep log in to < 10 seconds max.
View Replies !
Select .. Into Outfile Write
global scope: trying to execute on a schedule a .sql file which writes out a .csv file. windows(dont ask)/mysql in the batch file i have mysql -u myUser --password=somePass < myFile.sql the sql query in the file Code: use mydb; select field1,field2,field3 union select `actField1`,`actField2`,`actField3` into outfile 'F:folder est.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' FROM `detailsTable`;
View Replies !
Create/write To The File
I am working on Solaris and seem to get the following error Cant create/write to the file '/home/dslab456/temp/tempoutput.lst' As in windows i cannot specify which drive it is or so ? could anyone help me out with this ? SELECT * INTO OUTFILE '/home/dslab456/temp/tempoutput.lst' FROM stocklist_vokus s,p_tmpr p WHERE s.STOCKLIST =p.stocklist_value ORDER BY p.rid Edited 1 time(s). Last edit at 02/12/2007 05:38AM by summer queen.
View Replies !
Read & Write Locks
I need to lock some of my tables for a while with lock tables. However I find how the READ and WRITE locks is implemented in the MyISAM storage engine a little odd. When obtaining a READ lock all updates are prevented, including writes from the thread obtaining it. WRITE locks gives exclusive access to the issuing thread. However what I want is to give read and write priveleges to one thread and read priveleges to all other threads, and I can't figure out how to obtain this effect with lock tables... Is it even possibel?
View Replies !
Vita And Write In MySQL
My web site use a odbc to a MySQL database. I create it on XP and it's worked but I'm now on vita and the data base on a 2003 server. All the reading access on the database are ok but all the writing access are denied. I haven't error message just error 500 on ie7.
View Replies !
Re-write Text File
i'm creating a log file regarding database operations. My problem is i want to append a new record into the log file each time i update my database. Previously I've used the following method to create a file: SELECT * INTO OUTFILE 'c:/"+fileName[i]+" ' FIELDS TERMINATED BY '|' LINES TERMINATED BY ' ' FROM "+tableName[i]+"; MY problem is I cannot re-use the file created. Is there any way to call the same file for the purpose of re-use. Or maybe the OUTFILE do not allow file created to be re-use.
View Replies !
Write Once Read Many Field
I am designing a database and one of the requirements my client has is that once data is entered into certain fields it cannot, under any circumstances, be changed. Is it possible to create a write once read many (WORM) field in MySQL directly?
View Replies !
Write Text Files
I'm wondering if it's possible to write text files from a trigger/procedure. For instance, if I want to write a log file, for debugging, that could trace my procedure. I'm a Oracle developer, and in that environment I can use UTL_FILE to write txt files. Is there something similar in MySQL ?
View Replies !
|
TRACKER
