Problems finding the MAX value
| fname | MAX(b.bid) | ++++ | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b448) | Lisa | 3. | | 2 dozen chocolate chip cookies (30c106)| Anne | 11. | | 2 dozen chocolate chip cookies (a3aa96)| Donna | 7. | | 2 dozen chocolate chip cookies (d8e539)| Cheryl | 8. | | 2 Gold Rings (d9c17f) | Mandy | 26. | ++++ 5 rows in set (0.08 sec) mysql In this example, I think that the only reason the second batch of 2 dozen chocolate chip cookies winner is correct is because she was also the first bidder. I even tried to create a separate table with just the winners and work from that, but with the wrong results: mysql CREATE TABLE winners SELECT auction, bidder, MAX(bid) AS winningbid FROM PHPAUCTION_bids GROUP BY auction; Query OK, 205 rows affected (0.01 sec) Records: 205 Duplicates: 0 Warnings: 0 mysql SELECT CONCAT(a.title, (, LEFT(w.auction,6), )) AS itemname, SUBSTRING_INDEX(u.name, , 1) AS fname, winningbid FROM winners AS w, PHPAUCTION_users AS u, PHPAUCTION_auctions AS a WHERE w.auction=a.id AND w.bidder=u.id ORDER BY itemname LIMIT 5; +---+++ | itemname | fname | winningbid | +---+++ | 101 Dalmations, Oliver Company, A Goofy Movie - VHS(d7b448) | Lisa | 3. | | 2 dozen chocolate chip cookies(30c106)| Anne | 11. | | 2 dozen chocolate chip cookies(a3aa96)| Donna | 7. | | 2 dozen chocolate chip cookies(d8e539)| Cheryl | 8. | | 2 Gold Rings(d9c17f) | Mandy | 26. | +---+++ 5 rows in set (0.03 sec) mysql I think the way to do this is with a nested SELECT, but I couldn't make any of my attempts work. Could someone please help me construct an SQL query which would just print out the correct winners for each auction? I'm using MySQL 4.0.24 in Debian sarge. Thanks for your advice and suggestions. -Kevin Zembower - E. Kevin Zembower Internet Systems Group manager Johns Hopkins University Bloomberg School of Public Health Center for Communications Programs 111 Market Place, Suite 310 Baltimore, MD 21202 410-659-6139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems finding the MAX value
Man, you're awesome. Only two typos in the whole procedure. However, for the life of me, I'm puzzled over how it works. If you have more patience can you explain? Correct query (table is singular, not PHPAUCTIONS_...): CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid FROM PHPAUCTION_bids GROUP BY auction; The temporary table tmpWinners doesn't even contain a field for the ID of the winning bidder. This is the first puzzling point and significant diference between our two solutions. Correct query (changed line 8 from AND b.bit...): SELECT CONCAT(a.title, (, LEFT(b.auction,4), )) AS FullTitle , SUBSTRING_INDEX(u.name, , 1) AS fname , b.bid FROMtmpWinners AS w INNER JOIN PHPAUCTION_bids AS b ON b.auction = w.auction AND b.bid = w.winningbid INNER JOIN PHPAUCTION_users AS u ON b.bidder=u.id INNER JOIN PHPAUCTION_auctions AS a ON b.auction=a.id ORDER BY FullTitle LIMIT 5; - \g +--+---+-+ | FullTitle| fname | bid | +--+---+-+ | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Saori | 3. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 11. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 7. | | 2 dozen chocolate chip cookies (d8e5)| Donna | 8. | | 2 Gold Rings (d9c1) | t | 26. | +--+---+-+ 5 rows in set (0.15 sec) mysql So, the SELECT query goes through the tmpWinniing table and, for each record, finds a record in the bids table that has the same item ID and same bid price. It then uses the bidder's ID it found to look up the bidder's name, and the auction item's ID to look up the item's title. It seems to me that the significant difference between our solutions is your use of two conditional clauses in the INNER JOIN between tmpWinner and PHPAUCTION_bids. I don't think I've ever seen a join done on more than one field between two tables before. Would this query still work if more than one person bid the same amount on the same item? The business rules built into phpAuction prevent this, but in a more generic situation, would this query still work correctly? I guess one anwer to this is 'yes,' because the MAX() function returns the first of two equal maximum values it finds, doesn't it? Despite my puzzlement at how you were able to come up with such a great solution, I'm very grateful for your help and explanations. If you're ever in Baltimore, MD, I owe you a beer. Thanks. -Kevin [EMAIL PROTECTED] 11/04/05 11:22AM This is such a FAQ that they put the answer in the manual: http://dev.mysql.com/doc/refman/5.0/en/index.html What you are looking for is the row that contains the maximum bid for each itemname. The easiest first step it to actually determine what the highest bid for each item actually is then use that information to build the rest of what you wanted. The most portable and easiest to write solution to this is the two-table method ( I think I divined your column names correctly, maybe not...): # begin example # CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid FROM PHPAUCTIONS_bids GROUP BY auction; SELECT CONCAT(a.title, (, LEFT(b.auction,4), )) AS FullTitle , SUBSTRING_INDEX(u.name, , 1) AS fname , b.bid FROMtmpWinners AS w INNER JOIN PHPAUCTION_bids AS b ON b.auction = w.auction AND b.bit = w.winningbid INNER JOIN PHPAUCTION_users AS u ON b.bidder=u.id INNER JOIN PHPAUCTION_auctions AS a ON b.auction=a.id ORDER BY FullTitle LIMIT 35; DROP TEMPORARY TABLE tmpWinners; # end example # Does that make sense? You should be able to expand on that pattern to build whatever list you want. I showed the full-chain of how each table relates to another but you could have simplified the query above, can you see where? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help comparing MySQL to MS SQL Server
I'm a system administrator for a small (200 people) branch of a large university/medical school. I've worked with MySQL and use it as my database of choice for web-based dynamic content. I would not consider myself an experienced, professionally-trained, knowledgeable database administrator, more of a database user who's had to administer his own database systems because no one else's around. My organization is trying to decide on an SQL engine for general purpose database work within our organization. The one professional database administrator we have works mainly in MS Access, but is looking forward to building on her beginner-level understanding of SQL and becoming an SQL administrator. Right now, the largest database in our organization is a flat-file structure with less than 500,000 records in it, which could conceivably grow ten-fold in the next five years. The organization hired an outside consultant to evaluate which SQL engine to go with. This is what he sent to us: === MySQL is an open-source database management system (DBMS). It uses client/server architecture and is a multi-threaded, multi-user database server. MySQL was designed for speed; therefore, it does not provide many of the features provided by relational database systems, such as sub-queries, foreign keys, referential integrity, stored procedures, triggers, and views. In addition, it contains a locking mechanism that is not adequate for tables containing many write actions occurring simultaneously from different users. It is also lacking in reference to support for software applications and tools. SQL Server 2000 is a complete Relational Database Management System (RDBMS) that also includes integrated analysis functionality for OLAP and data mining. SQL Server 2000 meets the data and analysis storage requirements of the largest data processing systems and commercial Web sites, yet at the same time can provide easy-to-use data storage services to an individual or small business. The architecture of Microsoft SQL Server supports advanced server features, such as row-level locking, advanced query optimization, data replication, distributed database management, and Analysis Services. Transact-SQL (T-SQL) is the SQL dialect supported by SQL Server 2000. === I don't know whether the consultant wrote this himself, or if it came from somewhere. It could be Microsoft advertizement, for all I know. Most of the terms aren't familiar to me, like sub-queries or referential integrity. I feel out of my depth evaluating this comparison. My questions are: 1. Is this a fair comparison of MySQL and MS SQL Server 2000? 2. Is this up to date with the current status of MySQL? 3. Would the deficiencies pointed out in MySQL, if true, apply to the type of work we envision? Granted, I haven't given you all much information about what we hope to do with an SQL engine, but I don't think it will be very sophisticated. Thank you for all your thoughts and comments. -Kevin Zembower - E. Kevin Zembower Unix Administrator Johns Hopkins University/Center for Communications Programs 111 Market Place, Suite 310 Baltimore, MD 21202 410-659-6139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
Martijn, thank you very much for your analysis. I hope others will continue to join in. With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? Thanks, again, for your thoughts. -Kevin Martijn Tonies [EMAIL PROTECTED] 11/07/03 12:12PM 3. Would the deficiencies pointed out in MySQL, if true, apply to the type of work we envision? Granted, I haven't given you all much information about what we hope to do with an SQL engine, but I don't think it will be very sophisticated. If it's not very sophisticated, MySQL will do just fine. In my opinion. There are other free and open source DBMSes as well, that do have procedures, triggers, views, subqueries and the like. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
Nestor, thanks for your question. The platform will actually be dictated by the SQL engine, not the other way around, which is more typically the case. If we go with MS SQL Server, we'll build a separate host, NT I would guess, to host it. I'm only responsible for Unix and Linux boxes here, so it'll be the responsibility of another group. If we go with MySQL or PostgreSQL (the only databases I have any familiarity with), I'll probably be responsible for setting up and configuring a new Linux (Debian) host, and maintaining it. The in-house database administer would be the administrator, and I would just offer any help that I could, which might not be much. Thanks, again, for writing. -Kevin Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM I have not work with it but postgres is supposed to work great in /BSD/Linux/Unix/solaris environment Which platform are you using? :-) Nestor A. Florez Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Safe way to allow users to create databases?
Is there a safe way to allow ordinary users, who should not have MySQL root privileges, to create databases using any name they choose? It seems to me that I would need to use GRANT ALL ON *.* TO user WITH GRANT OPTION. This would be dangerous because of the other user's databases and the mysql database itself. All the reference books I have talk about the GRANT CREATE privilege with regard to tables, not databases. Any suggestions? Thank you. -Kevin Zembower - E. Kevin Zembower Unix Administrator Johns Hopkins University/Center for Communications Programs 111 Market Place, Suite 310 Baltimore, MD 21202 410-659-6139 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Duplicating records with a changed field?
Is it possible to duplicate a record in a table into the same table with a changed field? I have a single user who now has authority over an additional database. I wanted to duplicate the records in myslq.db for this user, changing the Db field to the name of the additional database. I searched the mailing list archives, and found a reference to this quote from the INSERT...SELECT manual page: The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it's forbidden in ANSI SQL to SELECT from the same table into which you are inserting. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using subselect clauses, the situation could easily be very confusing!) It seems to me that this absolutely forbids it. Is there a work around? another method? [I'm aware that I could have just typed the whole thing in an INSERT statement more quickly and with fewer keystrokes that typing this note, but I'm the curious type.] Thanks for your thoughts. -Kevin Zembower - E. Kevin Zembower Unix Administrator Johns Hopkins University/Center for Communications Programs 111 Market Place, Suite 310 Baltimore, MD 21202 410-659-6139 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie: Can't get GRANT to work
Thank you, Carl and Chris, for helping me with this problem. Something else must be happening, as I still can't log in: www:~ # mysql -u zope -p Enter password: ERROR 1045: Access denied for user: 'zope@localhost' (Using password: YES) www:~ # Mysqlaccess shows: www:~ # mysqlaccess -u zope -d zope -P Could not open outputfile ~/mysqlaccess.log for debugging-info mysqlaccess Version 2.06, 20 Dec 2000 Password for MySQL superuser : Access-rights for USER 'zope', from HOST 'localhost', to DB 'zope' +-+---+ +-+---+ | Select_priv | N | | Shutdown_priv | N | | Insert_priv | N | | Process_priv| N | | Update_priv | N | | File_priv | N | | Delete_priv | N | | Grant_priv | N | | Create_priv | N | | References_priv | N | | Drop_priv | N | | Index_priv | N | | Reload_priv | N | | Alter_priv | N | +-+---+ +-+---+ BEWARE: Everybody can access your DB as user `zope' from host `localhost' : WITHOUT supplying a password. : Be very careful about it!! BEWARE: Accessing the db as an anonymous user. : Your username has no relevance The following rules are used: db: 'No matching rule' host : 'Not processed: host-field is not empty in db-table.' user : 'localhost','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N' www:~ # Any suggestions for further diagnostics and solutions? Thanks, again, everybody, for your help. -Kevin Zembower Carl Troein [EMAIL PROTECTED] 11/14/01 02:47PM KEVIN ZEMBOWER writes: mysql grant all on zope.* to zope identified by xxx; Query OK, 0 rows affected (0.00 sec) mysql select * from user where user=zope; | %| zope | 34577362486f3680 | N | N | N | N | N | N | N | N | N| N | N | N | N | N | My questions: 1. Why didn't this work? It did work. You granted all privileges on the database zope, but the privileges in the 'user' table are the _global_ privileges. Those are what you set with GRANT ... ON *.* ... To see the ones you set, have a look in the table 'db'. 2. If it had worked, would I have seen something other than zero records affected in response to the GRANT command? For some reason you always get a 0 back from GRANT. There's probably some internal reason for this. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie: Can't get GRANT to work
I'm certain I'm making a simple mistake here, but can't find it. Here's what I'm executing, using mysql 3.23.43-log: [I'm root when I run this] #mysql -p mysql mysql grant all on zope.* to zope identified by xxx; Query OK, 0 rows affected (0.00 sec) mysql select * from user where user=zope; +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | Host | User | password | Select_priv | Insert_priv | Update_priv | |Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | |File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | %| zope | 34577362486f3680 | N | N | N | N | | N | N | N | N | N| N || N | N | N | N | +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ 1 row in set (0.00 sec) mysql My questions: 1. Why didn't this work? 2. If it had worked, would I have seen something other than zero records affected in response to the GRANT command? I search the recent messages on GRANT and found the suggestion to run mysql_fix_privilege_tables. It seemed to run correctly, but didn't change anything. Thanks for helping this newbie. -Kevin Zembower - E. Kevin Zembower Unix Administrator Johns Hopkins University/Center for Communications Programs 111 Market Place, Suite 310 Baltimore, MD 21202 410-659-6139 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie: Can't get GRANT to work
Yes, I've read this a number of times too. I've also read the section that says If you use GRANT, you don't need to use FLUSH PRIVILEGES. The way that I read this is that GRANT is the automatic method, not requiring FLUSH PRIVILEGES, whereas modifying the tables directly, using INSERT, UPDATE, etc. does require the use of FLUSH PRIVILEGES. Here's the proof: www:~ # mysql -p mysql mysql grant all on zope.* to zope identified by ccpzope; Query OK, 0 rows affected (0.01 sec) mysql FLUSH PRIVILEGES; Query OK, 0 rows affected (0.02 sec) mysql select * from user where user=zope; +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | Host | User | password | Select_priv | Insert_priv | Update_priv | |Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | |File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | %| zope | 34577362486f3680 | N | N | N | N | | N | N | N | N | N| N || N | N | N | N | +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ 1 row in set (0.01 sec) mysql Thanks for trying to give me a hand. I'm open to other suggestions. -Kevin Zembower Chris Cooper [EMAIL PROTECTED] 11/14/01 02:46PM http://www.mysql.com/doc/P/r/Privilege_changes.html If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise your changes will have no effect until you restart the server. If you change the grant tables manually but forget to reload the privileges, you will be wondering why your changes don't seem to make any difference! HTH, -- coop On Wed, 2001-11-14 at 13:29, KEVIN ZEMBOWER wrote: I'm certain I'm making a simple mistake here, but can't find it. Here's what I'm executing, using mysql 3.23.43-log: [I'm root when I run this] #mysql -p mysql mysql grant all on zope.* to zope identified by xxx; Query OK, 0 rows affected (0.00 sec) mysql select * from user where user=zope; +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | Host | User | password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | %| zope | 34577362486f3680 | N | N | N | N | N | N | N | N | N| N | N | N | N | N | +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ 1 row in set (0.00 sec) mysql My questions: 1. Why didn't this work? 2. If it had worked, would I have seen something other than zero records affected in response to the GRANT command? I search the recent messages on GRANT and found the suggestion to run mysql_fix_privilege_tables. It seemed to run correctly, but didn't change anything. Thanks for helping this newbie. -Kevin Zembower - E. Kevin Zembower Unix Administrator Johns Hopkins University/Center for Communications Programs 111 Market Place, Suite 310 Baltimore, MD 21202 410-659-6139 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
