grouping
Hi, I want to use: select column1, column2, max(column3) as maximum from table_name group by column 1; Please tell me if the values from column2 will contain the values from those records where the column3 has the maximum value. If it doesn't, please tell me how to do this as fast as possible. I know that I could get the maximum values, than make another query and get the values from the lines that have that max value, but I think this will take too much time. Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need a help in Query
Hi Fello MySQL users, I am Bala Raju, i have a problem to extract data by writing query, i need u r help. Please help me. I have two tables, i am giving the tables as attachement, firs table is playersloginfo and second one is playerhanddetails. playersloginfo : This table stores all the loginsession details of the players, who visited the game. playerhanddetails: This table stores all the games the player played during a session. I need to extract the data from both these tables, i need to display the player's logintime, logouttime and the totaltime per a session(these are available in 1 table) and number of games the player played(from 2 table). Please give some ideas to write this queries. (I tried in this way to extract data, i simple selected all the columns and used time difference function, but to find number of games by a player, i need to use second table(playerhanddetails). I am searching second table by conditions of user_name, and date between (logindate, logoutdate) and time between (logintime, logouttime)) playersloginfo: -- ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | user_name | varchar(20) | YES | | NULL| | | ip | varchar(16) | YES | | NULL| | | action | varchar(15) | YES | | NULL| | | logindate | date| YES | | NULL| | | logoutdate | date| YES | | NULL| | | logintime | time| YES | | NULL| | | logouttime | time| YES | | NULL| | | status | varchar(15) | YES | | NULL| | ++-+--+-+-+---+ playerhanddetails : - ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | playername | varchar(20) | YES | | NULL| | | handnumber | bigint(20) | YES | | NULL| | | date | date| YES | | NULL| | | time | time| YES | | NULL| | ++-+--+-+-+---+
Re: need a help in Query
On Saturday 10 February 2007 12:45, balaraju mandala wrote: Hi Fello MySQL users, I am Bala Raju, i have a problem to extract data by writing query, i need u r help. Please help me. I have two tables, i am giving the tables as attachement, firs table is playersloginfo and second one is playerhanddetails. playersloginfo : This table stores all the loginsession details of the players, who visited the game. playerhanddetails: This table stores all the games the player played during a session. I need to extract the data from both these tables, i need to display the player's logintime, logouttime and the totaltime per a session(these are available in 1 table) and number of games the player played(from 2 table). Please give some ideas to write this queries. (I tried in this way to extract data, i simple selected all the columns and used time difference function, but to find number of games by a player, i need to use second table(playerhanddetails). I am searching second table by conditions of user_name, and date between (logindate, logoutdate) and time between (logintime, logouttime)) You should redesign your table playersloginfo. logindate and logintime should be merge into one field: login DATETIME The same goes for logoutdate and logouttime: logout DATETIME. Then you query will be much easier to handle. You should also change ip from varchar(15) to INT UNSIGNED and use INET_ATON to convert a ip of the form a.b.c.d to unsinged int, and INET_NTOA to go back to a.b.c.d -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_upgrade shows errors
Hello, I noticed that the current MySQL 5.0 release is not available as binary, so I downloaded the source and compiled it on my testing machine. Compilation went fine and I can connect to the new MySQL server version. But then I tried to run the mysql_upgrade script to fix possible issues and here's what it gave me: ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' @hadGrantPriv:=1 1 1 ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type' ERROR 1061 (42000) at line 66: Duplicate key name 'Grantor' ERROR 1054 (42S22) at line 102: Unknown column 'Type' in 'columns_priv' ERROR 1060 (42S21) at line 124: Duplicate column name 'type' @hadShowDbPriv:=1 1 1 (and some more similar stuff) I tried it twice. At the first time, all other tables had an OK besides them, at the second time, those lines didn't show up anymore. MySQL server is version 5.0.33, OS is Debian Linux 3.1, previous MySQL version was 5.0.17, installed from the binary release. What do the above error messages mean? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grouping
select column1, column2, max(column3) as maximum from table_name group by column 1; Please tell me if the values from column2 will contain the values from those records where the column3 has the maximum value. They will not. please tell me how to do this as fast as possible. What's fastest depends on your table, indexes c. Here is one way... select t1.column1, (select column2 from table_name t2 where t2.column1=t1.column1) as column2, max(t1.column3) as maximum from table_name t1 group by column1; and here is another, usually faster. select t1.column1, t1.column2, t1.column3 from table_name t1 left join table_name t2 on t1.column1=t2.column1 and t1.column3t2.column3 where t2.column1 is null; PB Octavian Rasnita wrote: Hi, I want to use: select column1, column2, max(column3) as maximum from table_name group by column 1; Please tell me if the values from column2 will contain the values from those records where the column3 has the maximum value. If it doesn't, please tell me how to do this as fast as possible. I know that I could get the maximum values, than make another query and get the values from the lines that have that max value, but I think this will take too much time. Thank you. Octavian -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.34/679 - Release Date: 2/10/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need a help in Query
Hi Jorn, Thank you for reply, of course i can merge the columns and change the datatype. But buddy that is not problem here, the problem is extract the data. Did u able to understand my language, if not i will explain you again. Please read the mail again and tell some ways from u r experience. I am running out of solutions, as i am working on this from last two days.
Re: need a help in Query
On Saturday 10 February 2007 19:21, balaraju mandala wrote: Hi Jorn, Thank you for reply, of course i can merge the columns and change the datatype. But buddy that is not problem here, the problem is extract the data. After reading your message once more, I realise that you should do even more changes. The username should not be a part of the playersloginfo table. Consider this: Table: userinfo id mediumint unsigned not null auto_increment, user_name varchar(20) Table: playersloginfo user_id mediumint unsigned not null comment '--userinfo.id', ip logint unsinged, action varchar(15), login datetime, logout datetime, status varchar(15) Table: playershanddetails playername mediumint unsigned not null comment '--userinfo.id', handnumber bigint(20), date_time datetime Your query might look something like. select u.user_name,l.login,l.logout,timediff(l.login,l.logout) as totaltime, count(h.*) as no_of_games from userinfo as u inner join playersloginfo as l on (l.user_id=u.id) inner join playershanddetails as h on (h.playername=u.id) group by u.id; Please note: I have not tried this... just a quick suggestion right out of my brain... :-) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table definitions not accepting data as defined...
OK - this has me TOTALLY stumped. I am running into cases where table definitions are somehow not working. I have a variable in a table defined: price decimal(5,2) Recently we moved servers, all of a sudden, this definition seems to read that the TOTAL length of that field is 5, not the pre-decimal place! Meaning, I get only a definition of 3,2! I thought this was an isolated incident, some quirk of the server move, and just increased the decimal definition and went on with life, however a more serious bug of some sort has popped up. I have a variable definition of: large_title varchar(50) That field however, will accept ONLY 36 characters, no more. I have eliminated the possibility of the bug being in the code, and have run all the integrity checks on the database - no problems there. The ONLY thing that I can see that could possibly affect this is that the Collation for the fields is latin1_swedish_ci - but that was the same on the old server to. This has only been occuring on the new server. The new server is running MYSQL 5.0.22, wheras the old server was V 4.something. Is there some nuance of the Collation that I am missing that would cause a 50 character definition to only accept 36? Some sort of massive DB failure happening that I should be checking on? Am I just seeing things? Has anyone else run into this, and does anyone have any suggestions to fix this? I have tried editing the field (through phpMyAdmin) to force the 50 characters, in case there was some weird thing happening where the characters defined was messed up or something - but no luck. My worry is that it is a symptom of a wider failure that I need to be aware of. Any help is mucho appreciated. The table definition for the latest table affected with this: (note - I have already changed that large_title field to 75 chars to accommodate the customers need for the 50 characters here - this is provided in case I missed something about the table that is important...) CREATE TABLE `email_updates` ( `id` int(10) unsigned NOT NULL auto_increment, `month` char(2) default NULL, `year` varchar(4) default NULL, `large_title` varchar(75) NOT NULL, `small_title` varchar(30) NOT NULL default '', `image_1` varchar(50) default NULL, `image_1_caption` varchar(100) default NULL, `image_2` varchar(50) default NULL, `image_2_caption` varchar(100) default NULL, `english` text, `spanish` text, `active` char(1) default 'n', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7096 ; -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
to join or not to join, that is the query
Hi, i am having some difficulty to write a query for the following problem: I have three tables: TABLE Person - id_person, name, id_levelA, id_sizeA, id_levelB, id_sizeB TABLE Levels - id, desc TABLE Sizes - id, desc I need a query that returns everything from the Person table, replacing the id_levelA... with the desc from the tables Levels and Sizes. I can get a result with one of them replaced using JOIN, but not several replacements using the same reference tables (levels and sizes). :-P Heres what i need as a result: - id_person, name, descA, sizeA, descB, sizeB descA, etc, being the id_levelA, etc replaced, and i assume i need to give it a new name to fetch the results, right? Can you guys point me in the right direction? Thanks! Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Capacity/Load question
Hello, Just looking for a little feedback before deciding on a data engine path and configuration. We're working on an application that is expected to have a high growth rate over the coming years. We're at the stage of designing the backend databases to maximize performance while keeping costs at a minimum. (I know, everyone's heard this one before) -- database already exists but in a small controlled environment. Here is the overall gist of the database. We have a couple tables that will be update infrequently but read from 100-200 times per second. We also have a couple tables that will be updated up to a couple hundred times per second. The tables that are read may only have 50k rows in them, the tables that are written will continue to grow (expecting 50M rows per year). Here is my basic idea on the overall design on the system (based upon how the app will work). The app has 3 distinct parts to it. The first part (A) will primarily do lookups (50K rows - Client table). The second part (B) will take transactional information and insert it into a database (50M rows - Transaction table). The third part (C) of the app will be used to query the transactional data. I was thinking of creating a master database that will hold the basic information needed across the three processes. Since parts A, B and C will all need to access the Client tables, I thought that maybe I should create a master database for Client Table. We will call this ServerA. From there I figure we can create replicated slave ServerA-Nodes that all of the processes can read from in a load balanced scenario (using IPVSADM). This is the easy part. The part B is heavy write and part C is heavy read I figure we can use a similar scenario. Have a single large cluster for part B's writes (including the use of table partitions) on ServerB and then create replicated slave ServerB-nodes that all of the part C processes would read from. Replication has to be near real time. That is, we have a contractual agreement to report the incoming processed data within 30 minutes. General table information Client table, multiple tables, 1:many Table A, 500 bytes ~200 read/s (50K records) Table B, Text, 3000 bytes, rarely read (mostly contact info) Table C, 250 bytes medium ~1 read/s (150K records) Transaction table, multiple tables, each 1:1 Table A, 400 bytes ~200 insert/s (peak 500 insert/s, low 10 insert/s) (50M records) Table B, 200 bytes ~50 insert/s (peak 200 insert/s, low 10 insert/s) (15M records) Table C, Text, 2000 bytes ~200 insert/s (peak 500 insert/s, low 10 insert/s) (50M records) So, in my end design I'm thinking two master clusters, one for Client tables, one for Transaction tables, and slave everything else (into two distinct groups). Anyone see any downside to this? Any better suggestions? Also, I've been looking at some data partitioning schemes. Would this add any performance impacts in the long run (by allowing me to put the different files on different drive arrays in the future). For the hardware clusters I'm looking to use dual dual core AMD's, 8gb ram, raid 5, for the slaves single dual core AMD's, 4gb ram, raid 5. All GB nic. Any feedback would be greatly appreciated. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Password on DB Files not on DB server
Hi, I am sure this is been asked many times before, but seems like I could not find answer to it so here you go, Is there any way to put password on Data base(files) itself rather than DB server? If yes how to achive it? If no, any suggestions which db support that? Thank you in advance! Suhas
Re: Password on DB Files not on DB server
At 10:49 PM 2/10/2007, Suhas Pharkute wrote: Hi, I am sure this is been asked many times before, but seems like I could not find answer to it so here you go, Is there any way to put password on Data base(files) itself rather than DB server? See the Grant command to set up users where you can allow them access to certain tables. http://dev.mysql.com/doc/refman/5.1/en/grant.html If yes how to achive it? If no, any suggestions which db support that? Here is an article that may help. http://www.securityfocus.com/infocus/1667 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Password on DB Files not on DB server
This is true, if the db is still on same db server. Correct me if I am wrong, but it will not protect if some body copies the files to other DB server, then they can see the DB Suhas On 2/10/07, mos [EMAIL PROTECTED] wrote: At 10:49 PM 2/10/2007, Suhas Pharkute wrote: Hi, I am sure this is been asked many times before, but seems like I could not find answer to it so here you go, Is there any way to put password on Data base(files) itself rather than DB server? See the Grant command to set up users where you can allow them access to certain tables. http://dev.mysql.com/doc/refman/5.1/en/grant.html If yes how to achive it? If no, any suggestions which db support that? Here is an article that may help. http://www.securityfocus.com/infocus/1667 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Password on DB Files not on DB server
This is true, if the db is still on same db server. Correct me if I am wrong, but it will not protect if some body copies the files to other DB server, then they can see the DB Suhas On 2/10/07, mos [EMAIL PROTECTED] wrote: At 10:49 PM 2/10/2007, Suhas Pharkute wrote: Hi, I am sure this is been asked many times before, but seems like I could not find answer to it so here you go, Is there any way to put password on Data base(files) itself rather than DB server? See the Grant command to set up users where you can allow them access to certain tables. http://dev.mysql.com/doc/refman/5.1/en/grant.html If yes how to achive it? If no, any suggestions which db support that? Here is an article that may help. http://www.securityfocus.com/infocus/1667 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Password on DB Files not on DB server
This is true, if the db is still on same db server. Correct me if I am wrong, but it will not protect if some body copies the files to other DB server, then they can see the DB Suhas
