Re: Display more than 2500 rows
Velen wrote: Hi, May be it's not the right forum i'm posting to. I have a Mysql Query : Select a.code,b.description, b.other_details,a.qty,a.price from xyz a, bcd b where a.code=b.code and a.id='5' order by a.id This is running fine but when using VB6 to display it in a Msflexgrid, it's a nightmare! It will take about 3-5 mins to display around 2500 rows. I'm not sure what you mean when you say it is "running fine". If that means that you are getting the right result but it's taking too long, that's one thing. If you mean that the speed is fine until you get VB6 involved, that's another. Try the query in MySQL itself and see how long it takes to display the data. If the speed is perfectly acceptable in raw MySQL but dreadful when you use VB6, you've pretty much proven that VB6 is the problem, not MySQL. In that case, you may want to ask on a VB6 newsgroup for hints in making it perform better. Can anyone suggest a better alternative to Msflexgrid or how to improve the speed on msflexgrid? Thanks. Regards, Velen -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where column
- Original Message - From: "Olav Mørkrid" <[EMAIL PROTECTED]> To: Sent: Friday, August 24, 2007 1:07 AM Subject: where column hello does anyone know what is returned when you do a where column without further parameters? SELECT * FROM TABLE WHERE COLUMN; for integer columns it seems to return non-zero columns, but for other types of columns the results seemed unpredictable. In my opinion, the statement should not execute at all since it isn't syntactically correct. In the dialects of SQL I have used - and I've been using SQL for a lot of years - simply saying "WHERE hiredate" (or whatever column name you want) is an incomplete statement since the column name must be followed by some kind of operator, such as =, <, >, LIKE, or whatever. Despite that, I am not up-to-date on MySQL and they may support an extension that lets you write SQL like that; in that case, the MySQL manual for your version should make it clear what happens if you write that. But I still think it should not execute at all. The WHERE clause is a filter to prevent rows that don't satisfy the condition from appearing in your result set; "WHERE columnname" is not a complete condition in my opinion so it simply should execute. -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with delete query
- Original Message - From: "Ben Liu" <[EMAIL PROTECTED]> To: Sent: Thursday, June 14, 2007 3:11 PM Subject: need help with delete query I'm trying to delete a subset of items in a table. The problem is, I don't want to query for the subset first, store the results and then run a series of delete queries. I believe this is a rather basic issue that has a well-accepted, simple solution which I am ignorant of. There are two relevant tables: order_items (individual items in a particular order) item_id ord_id orders (order details such as person's name, address, phone number, etc) ord_id cust_id I have set all "shopping carts" or initiated/incomplete orders so that orders.cust_id=-1 I wish to "clear all shopping carts" so I need to delete two sets of items: 1) All records in order_items where order_items.ord_id=orders.ord_id and orders.cust_id=-1 2) All records in orders where orders.cust_id=-1 I can do #2 easily enough, but I can't figure out how to write the delete query for #1. I've tried: DELETE FROM order_items WHERE order_items.ord_id=orders.ord_id AND orders.cust_id=-1 DELETE FROM order_items LEFT JOIN orders ON order_items.ord_id=orders.ord_idWHERE orders.cust_id=-1 Neither of them seem to work. Thanks for any help. First of all, let me explain that I have been away from database work for about a year now and I have not kept up with developments in MySQL. However, I have been working with databases for many years, including many years with DB2 and a fair bit of work with MySQL until last summer. So, based on that experience, I'm going to offer you my opinions for what they're worth. First of all, the first DELETE that you cited should work. I don't see any reason why it wouldn't except possibly that there are no rows that satisfy the query. It should be easy to determine if there are any qualifying rows: simply turn the query into a SELECT and see if any rows satsify the query. Run: SELECT * FROM order_items WHERE order_items.ord_id=orders.ord_id AND orders.cust_id=-1 If you get no rows from that, then that's why your query failed. In that case, investigate the UDPATE queries that were supposed to be setting the cust_id to -1 and see what's wrong with them. As for the second DELETE, I am dubious that this could ever work but I'm not 100% certain. That query LOOKS like a join and, in DB2 at least, you can never delete rows from a join. Now, MySQL may tolerate that syntax and not interpret it as a join so you may want to confirm this with someone familiar with the version of MySQL you are using. So, in a nutshell, the first DELETE should work fine but you may not have the data there that will allow it to do anything. -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining number of vowels in a string
- Original Message - From: "Brent Baisley" <[EMAIL PROTECTED]> To: Sent: Thursday, May 31, 2007 1:50 PM Subject: Determining number of vowels in a string I'm trying to do a select that will return the number of vowels/consonants present in a string for each record. I thought a simple grep was the way to go, but it appears the grep functions only tell you if a string is present. I would even settle for a grep replace. Just replace the vowels with nothing and determine how the string length changed. Any ideas? I'm using v4.1. I'd rather do it in a sql statement rather than using a scripting language. Is your text always in English? Recognizing vowels in other languages and scripts could be very difficult; for instance, I'm not sure if Japanese even has the _concept_ of vowels or consonants. And even in English, "y" is sometimes considered a vowel and sometimes a consonant. At least it was when I was in primary school way back when. -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do I need 2 selects?
- Original Message - From: "James Lockie" <[EMAIL PROTECTED]> To: "MySQL Mailing List" Sent: Friday, May 25, 2007 3:11 PM Subject: do I need 2 selects? Say I have x rows of 2 columns each. For example, 2 rows: 'A 10:00pm' and 'A 11:00pm'. I want to return one 'A' row with the latest time (eg. 'A 11:00pm). I could do 'select distinct(col1) from table' and then 'select col1, max( col2 ) where col1=row[0]'? Is there a way to do it in one select? That's hard to answer. I have a feeling that this is not your _real_ question, just the essence of a bigger problem. That means that any answer I give to your specific question may not be very useful in handling the bigger question. For example, your ultimate goal may be to produce a report showing the maximum column 2 value for umpteen different values in column 1. So, given the following table: Col1Col2 A10:00 A9:00 A12:00 A14:00 A5:00 B23:00 B19:00 B22:30 C12:00 You might want a report that says: A14:00 B 23:00 C12:00 But taking your question at face value and assuming that there is only one unique value in Column 1, you could get what you want with a subquery. I don't know if you are familiar with them but subqueries are queries within a query. Subqueries are written within brackets and are always executed first. The subquery result gets plugged into the outer query and the subquery result controls the rows obtained by the outer query, which are then displayed. You need a subquery that determines the largest value of column 2 for all rows that contain A in column 1. That query is: select max(col2) from table where col1 = 'A' That subquery is only ever going to return 1 row, even if there are dozens of rows that contain the largest col2 value. Therefore, even if the table had several rows that said "A 11:00" and there were no rows that had an A value that was greater than 11:00, the max() function ensures that the value "11:00" is returned by the subquery, even if that value occurred many times in the table. The outer query is simply going to say: select col1, col2 from table where col2 = (insert subquery here) In other words, what is the col1 and col2 value on the row that contains the time returned by the subquery, which is the highest time for an "A" row. Therefore, the full query will be: select col1, col2 from table where col2 = (select max(col2) from table where col1 = 'A') So, play with that a bit and satisfy yourself that it works. Then, see if you can figure out the answer to your bigger question on your own, using the principles of this answer. If not, post again and perhaps someone can help you with your REAL question ;-) -- Rhino -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
