innodb recovery problems
Hi, I have been backing up via the dubious method of copying the database data folder onto another machine where it is properly backed up onto DLT. (yes, I know I should have used mysqldump!) Recovering some tables today I copied the files back into their position (including the ibdata1 file and the other id_* files) but the data is not recovered the table. There are no errors being given though. It is just as if I haven't recovered the file - the data is still missing The ibdata1 file and all the other id_* files from backup appear to be the same size as they are after the data had been deleted Any help greatly appreciated! Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb foreign key
Hi, I have two innodb tables produced as show below CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_147` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE, ) TYPE=InnoDB CREATE TABLE `gene` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `species` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB When trying to add a foreign key constraint as in: alter table monogenic add constraint foreign key (gene_ID) references gene (id) on delete cascade; the error below is produced: ERROR 1005: Can't create table './nugenob/#sql-4585_37.frm' (errno: 150) could anybody help? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb foreign key
whoops, yes, thanks, missed that Victor Pendleton wrote: One thing that stands out is the data types are different sizes. http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -Original Message- From: R.Dobson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: innodb foreign key Hi, I have two innodb tables produced as show below CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_147` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE, ) TYPE=InnoDB CREATE TABLE `gene` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `species` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB When trying to add a foreign key constraint as in: alter table monogenic add constraint foreign key (gene_ID) references gene (id) on delete cascade; the error below is produced: ERROR 1005: Can't create table './nugenob/#sql-4585_37.frm' (errno: 150) could anybody help? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
last_insert_id()
Hi, Is it possible to obtain the last_insert_id() for a particular column in a particular table? eg, say i wanted to obtain the last insert id of a column called id in table reference, something along the lines of: last_insert_id(reference.id) The reason I ask is because I want to initially insert values into two tables and then insert values into a third using the last_insert_id() from the first two tables. Obviously, the last_insert_id from the first insert is replaced by the last_insert_id from the second insert. This is all done in a perl script and I could store the first last_insert_id in a variable but I thought their might be a more elegant way round it? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb on delete cascade
Hi, yes, I should have included in the first mail. They are: mysql show table status like 'gene%'; +--+++--++-+-+--+---++-+-+++---+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-+-+--+---++-+-+++---+ | gene | InnoDB | Dynamic|0 | 0 | 16384 |NULL |0 | 0 | 1 | NULL| NULL| NULL || InnoDB free: 55296 kB | +--+++--++-+-+--+---++-+-+++---+ 1 row in set (0.00 sec) mysql show table status like 'name%'; +--+++--++-+-+--+---++-+-+++---+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-+-+--+---++-+-+++---+ | name | InnoDB | Dynamic|1 | 16384 | 16384 |NULL |0 | 0 | 10 | NULL| NULL| NULL || InnoDB free: 55296 kB | +--+++--++-+-+--+---++-+-+++---+ 1 row in set (0.00 sec) cheers Rich Egor Egorov wrote: R.Dobson [EMAIL PROTECTED] wrote: Hi, I have a db where I have converted all the tables innodb. I have 2 tables in particular called gene and name. They both have a primary key call id. I want the primary key from name to be deleted when the corresponding key is deleted from gene. It doesn't seem to be happening as yet! show columns from name; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | mediumint(8) unsigned | | PRI | NULL| auto_increment | | other_name | varchar(100) | | | || | other_symbol| varchar(100) | | | || | refseq_ID | varchar(20) | YES | | NULL|| | GO | varchar(20) | YES | | NULL|| | locus_link | varchar(20) | YES | | NULL|| | other_species_index | varchar(20) | YES | | NULL|| +-+---+--+-+-++ 7 rows in set (0.00 sec) mysql show columns from gene; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | mediumint(8) unsigned | | PRI | NULL| auto_increment | | name| varchar(100) | | | || | species | varchar(100) | | | || +-+---+--+-+-++ 3 rows in set (0.00 sec) mysqlalter table name add foreign key(id) references gene(id) on delete cascade; mysql select * from gene; ++--+-+ | id | name | species | ++--+-+ | 9 | hi | human | ++--+-+ 1 row in set (0.00 sec) mysql select * from name; +++--+---+--++-+ | id | other_name | other_symbol | refseq_ID | GO | locus_link | other_species_index | +++--+---+--++-+ | 9 | hi | human| i | i| i | i
Re: innodb on delete cascade
Hi, i'm using Distrib 4.0.1-alpha, for sun-solaris2.8 (sparc) Victoria Reznichenko wrote: R.Dobson [EMAIL PROTECTED] wrote: mmm, i've just tried the example within the mysql docs: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE cascade ) TYPE=INNODB; Now, insert a couple of lies of data: mysql insert into parent values(1); Query OK, 1 row affected (0.00 sec) mysql insert into parent values(2); Query OK, 1 row affected (0.00 sec) mysql insert into parent values(3); Query OK, 1 row affected (0.00 sec) mysql insert into child values(1,1); Query OK, 1 row affected (0.01 sec) mysql insert into child values(2,2); Query OK, 1 row affected (0.00 sec) mysql insert into child values(3,3); Query OK, 1 row affected (0.00 sec) mysql select * from child; +--+---+ | id | parent_id | +--+---+ |1 | 1 | |2 | 2 | |3 | 3 | +--+---+ 3 rows in set (0.00 sec) mysql select * from parent; ++ | id | ++ | 1 | | 2 | | 3 | ++ 3 rows in set (0.00 sec) When I come to try to delete some data from the parent table i'm getting errors as in: mysql delete from parent where id=1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails any thoughts? Worked perfect for me: mysql delete from parent where id=1; Query OK, 1 row affected (0.04 sec) mysql select * from child; +--+---+ | id | parent_id | +--+---+ |2 | 2 | |3 | 3 | +--+---+ 2 rows in set (0.02 sec) mysql select * from parent; ++ | id | ++ | 2 | | 3 | ++ 2 rows in set (0.00 sec) What version of MySQL do you use?
innodb on delete cascade
Hi, I have a db where I have converted all the tables innodb. I have 2 tables in particular called gene and name. They both have a primary key call id. I want the primary key from name to be deleted when the corresponding key is deleted from gene. It doesn't seem to be happening as yet! show columns from name; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | mediumint(8) unsigned | | PRI | NULL| auto_increment | | other_name | varchar(100) | | | || | other_symbol| varchar(100) | | | || | refseq_ID | varchar(20) | YES | | NULL|| | GO | varchar(20) | YES | | NULL|| | locus_link | varchar(20) | YES | | NULL|| | other_species_index | varchar(20) | YES | | NULL|| +-+---+--+-+-++ 7 rows in set (0.00 sec) mysql show columns from gene; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | mediumint(8) unsigned | | PRI | NULL| auto_increment | | name| varchar(100) | | | || | species | varchar(100) | | | || +-+---+--+-+-++ 3 rows in set (0.00 sec) mysqlalter table name add foreign key(id) references gene(id) on delete cascade; mysql select * from gene; ++--+-+ | id | name | species | ++--+-+ | 9 | hi | human | ++--+-+ 1 row in set (0.00 sec) mysql select * from name; +++--+---+--++-+ | id | other_name | other_symbol | refseq_ID | GO | locus_link | other_species_index | +++--+---+--++-+ | 9 | hi | human| i | i| i | i | +++--+---+--++-+ 1 row in set (0.00 sec) mysql delete from gene where id=9; Query OK, 1 row affected (0.00 sec) mysql select * from name; +++--+---+--++-+ | id | other_name | other_symbol | refseq_ID | GO | locus_link | other_species_index | +++--+---+--++-+ | 9 | hi | human| i | i| i | i | +++--+---+--++-+ 1 row in set (0.00 sec) mysql select * from gene; Empty set (0.00 sec) The entry from name should be deleted as well? TIA Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb on delete cascade
mmm, i've just tried the example within the mysql docs: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE cascade ) TYPE=INNODB; Now, insert a couple of lies of data: mysql insert into parent values(1); Query OK, 1 row affected (0.00 sec) mysql insert into parent values(2); Query OK, 1 row affected (0.00 sec) mysql insert into parent values(3); Query OK, 1 row affected (0.00 sec) mysql insert into child values(1,1); Query OK, 1 row affected (0.01 sec) mysql insert into child values(2,2); Query OK, 1 row affected (0.00 sec) mysql insert into child values(3,3); Query OK, 1 row affected (0.00 sec) mysql select * from child; +--+---+ | id | parent_id | +--+---+ |1 | 1 | |2 | 2 | |3 | 3 | +--+---+ 3 rows in set (0.00 sec) mysql select * from parent; ++ | id | ++ | 1 | | 2 | | 3 | ++ 3 rows in set (0.00 sec) When I come to try to delete some data from the parent table i'm getting errors as in: mysql delete from parent where id=1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails any thoughts? cheers,tia r Jeff Mathis wrote: I just looked at your table syntax. you've got two auto_increment pk columns. do you always have a 1:1 correspondence between the name and gene tables? would it not be better to have a gene_id column in name, put an index on it, and then issue: alter table name add foreign key(gene_id) references gene(id) on delete cascade; In fact, I'm not sure you can actually create the constraint as you currently describe it R.Dobson wrote: Hi, yes, I should have included in the first mail. They are: mysql show table status like 'gene%'; +--+++--++-+-+--+---++-+-+++---+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-+-+--+---++-+-+++---+ | gene | InnoDB | Dynamic|0 | 0 | 16384 |NULL |0 | 0 | 1 | NULL| NULL| NULL || InnoDB free: 55296 kB | +--+++--++-+-+--+---++-+-+++---+ 1 row in set (0.00 sec) mysql show table status like 'name%'; +--+++--++-+-+--+---++-+-+++---+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-+-+--+---++-+-+++---+ | name | InnoDB | Dynamic|1 | 16384 | 16384 |NULL |0 | 0 | 10 | NULL| NULL| NULL || InnoDB free: 55296 kB | +--+++--++-+-+--+---++-+-+++---+ 1 row in set (0.00 sec) cheers Rich Egor Egorov wrote: R.Dobson [EMAIL PROTECTED] wrote: Hi, I have a db where I have converted all the tables innodb. I have 2 tables in particular called gene and name. They both have a primary key call id. I want the primary key from name to be deleted when the corresponding key is deleted from gene. It doesn't seem to be happening as yet! show columns from name; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | mediumint(8) unsigned | | PRI | NULL| auto_increment | | other_name | varchar(100) | | | || | other_symbol| varchar(100
Re: storing PDF files in mysql ?????
hi, 2 ways to do this: a) don't store the file actually in the db but store the path to it. b)use the function LOAD_FILE(filename) to get the contents of a file as a string value and store as a blob. decide which to use depending on how many pics you have cheers Rich toby gibbson wrote: ok guyz more trouble now im supposed to convert all the text (from ms word) to PDF and put the pdf files in mysql db now i ve really got myslf stuck havent i can anyone plz hlp thnx a million stuck az usual toby . _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - 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 -- Richard Dobson Database Administrator MRC Bright Study Clinical Pharmacolgy St Bartholomew's and the Royal London School of Medicine and Dentistry Charterhouse Square London EC1M 6BQ Tel: 020-7882-5670 Mob: 07711-522926 Fax: 020-7882-5672 [EMAIL PROTECTED] - 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: excel to mysql
to load files into mysql: create a table: create table newtable (column1 int(10),column2 varchar(250)); import the data: load data infile '/path/to/file/file.csv' into table newtable fields terminated by ',' lines terminated by '\n' Alia Mikati wrote: Hello everybody I hope u can help me with this. I want to export tables from excel to mysql and i saved the file as .csv then clicked on browse to get Location of the textfile. But it doesnt work. I get for expl: SQL-query : 3,Technical 4,Technical 7,Technical 15,Technical 20,Technical 26,Technical 32,Technical MySQL said: You have an error in your SQL syntax near '3,Technical 4,Technical 7,Technical 15,Technical 20,Techn' at line 1 Plz how can I do it? Thx - 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 -- Richard Dobson Database Administrator MRC Bright Study Clinical Pharmacolgy St Bartholomew's and the Royal London School of Medicine and Dentistry Charterhouse Square London EC1M 6BQ Tel: 020-7882-5670 Mob: 07711-522926 Fax: 020-7882-5672 [EMAIL PROTECTED] - 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: Someone has to know something on MySQLGUI?? - PLZ HLP!
hi ,if you're on windows I would use mysqlfront. It has been discontinued but you can still get it from: http://mysqlfront.sstienemann.de/ alternatively, you could use urSQL: http://www.urbanresearch.com/software/utils/urbsql/ cheers Rich David Kramer wrote: Can anyone point me in the direction of some good doc on MYSQLGUI for Win2K? Looking for Install/Setup notes.. Also is the Flash Light Tool Kit needed to run the GUI? Thanks, DK David Kramer Software Developer Reflect.com Direct: 415.369.4856 Cell: 650.302.7889 - 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 -- Richard Dobson Database Administrator MRC Bright Study Clinical Pharmacolgy St Bartholomew's and the Royal London School of Medicine and Dentistry Charterhouse Square London EC1M 6BQ Tel: 020-7882-5670 Mob: 07711-522926 Fax: 020-7882-5672 [EMAIL PROTECTED] - 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: looking for sql gui application
Hi, the latest version of mysql supports ssl connections. 'phpMyAdmin' is a browser based gui written in php. If your apache has mod_ssl then you could connect securely. http://www.phpwizard.net/projects/phpMyAdmin/ Alternatively, you could also use something like 'mysqlgui' and set up ssh port forwarding. I would like to offer hyperlinks but i cannot fot the life of me get onto the mysql web site at the moment! cheers Rich Roma Gupta wrote: Hi Everybody, My organization is looking for some secure GUI tool for MYSql. If possible, something which use SSH. I am new to MySql. Can anybody highlight on this and suggest some tools. We use lynix with apache, tomcat installed on it and installed ver 3.23 of Mysql. Thanks in advance Roma - 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
Re: How can this Oracle Query converted to MySQL
create temporary table tmp SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID; SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID=tmp.IndustryID) Cheers Rich Arul wrote: SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) This is to Pass the Mail Server : sql,query - 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
Re: returning top two values
Hi, thanks for all of the replies to my query. I'm not sure that I explained my problem very well as the solutions received are solutions to the problem I described, but not the one I meant :-) (I don't think anyway) I have a table in the format: family | member | score 1 | 1 |10 1 | 2 |15 1 | 3 |12 1 | 4 |17 2 | 1 | 5 2 | 2 | 7 2 | 3 | 9 2 | 4 |10 3 | 1 | 4 3 | 2 | 8 3 | 3 | 2 I want the top 2 highest scorers for each family as in: family | member | score --- 1 | 4|17 1 | 2|15 2 | 4|10 2 | 3| 9 3 | 2| 8 3 | 1| 4 Thanks again, Rich mysql Jay Blanchard wrote: [snip] I want to retrieve members of each family that have the two highest scoring values for a column. i.e the max and second max. Is there a function similar to max() or greatest() that will return the top 2 values when grouping by family ID? [/snip] Try this query select foo, MAX(bar) from table order by bar DESC limit 2; HTH! Jay - 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
Re: returning top two values
be a better choice. -Rob (because I'm bored at work, that's why...) On 22/5/02 at 10:36 am, R.Dobson [EMAIL PROTECTED] wrote: Hi, thanks for all of the replies to my query. I'm not sure that I explained my problem very well as the solutions received are solutions to the problem I described, but not the one I meant :-) (I don't think anyway) I have a table in the format: family | member | score 1 | 1 |10 1 | 2 |15 1 | 3 |12 1 | 4 |17 2 | 1 | 5 2 | 2 | 7 2 | 3 | 9 2 | 4 |10 3 | 1 | 4 3 | 2 | 8 3 | 3 | 2 I want the top 2 highest scorers for each family as in: family | member | score --- 1 | 4|17 1 | 2|15 2 | 4|10 2 | 3| 9 3 | 2| 8 3 | 1| 4 Thanks again, Rich mysql - 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: in and not in
Hi, select table1.col_name FROM table1 left join table2 on table1.col_name = table2.colnmae where table2.clnmae is null Rich Inbal Ovadia wrote: Hi i want to do in mysql query like that: SELECT col_name FROM table1 NOT IN (select col_nmae FROM tablse2) how can i do that? thanks - 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
returning top two values
Hi, I have a table containing data on a number of families. I want to retrieve members of each family that have the two highest scoring values for a column. i.e the max and second max. Is there a function similar to max() or greatest() that will return the top 2 values when grouping by family ID? I can do this using perl in about three steps but would rather make life easier for myself if I can. Thanks in advance Rich mysql query - 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
