kkubek

I'm too lazy to create the table to test this, but does this work ? select b.name, a.name from cats a, cats b where a.parent = b.id and a.id =2

select a.*, b.c1, b.c2, b.c3 from table1 a, table2 b where a.e_id=b.e_id c1,c2 and c3 are the column names u want from table2

I'm not exactly clear on what you're trying to do, but if you want data from 2 identical tables, you'd use a UNION select * from table1 UNION select * from table2 You'd add a where clause to both select if you wanted only a subset

How about select unit_id,count(*) from squads group by unit_id

select count(*) as namecount, column_name_with_name from tab group by column_name_with_name order by namecount

How about: SELECT surname FROM names WHERE left(surname,1) in ('A','B','C' ......) A little better than the ugly OR

It's hard to give a complete answer without fully understanding your requirements, but based on a lot of assumptions, your schema is a very good starting point. A couple suggestions: Many of the columns should be changed to NOT NULL. As a matter of fact, it looks to me like every one should be NOT NULL. Even the iffy ones like image, couldn't be populated with "No Image" if one is not available. You'll want to add FKs also. For example, mr_mobiles should have an FK on comp_id that points to mr_complist. And, mr_mfeatures needs FK to mr_mobiles. You need more FKs than that but I don't want to make it too easy 8-) Not sure what other info you're looking for.

fenway, I tried changing tmpdir, by doing: TMPDIR=/usr/local/mysql (from Linux) and I still got the error 17. I also tried droppping indexes: mysql> ALTER TABLE kk DROP INDEX idx_orders_rosnumber; ERROR 1030 (HY000): Got error 127 from storage engine mysql> ALTER TABLE kk DROP INDEX idx_orders_recordid -> ; ERROR 1030 (HY000): Got error 127 from storage engine mysql>

Yup, same result ... the dreaded error 17

I know, that error 17 is driving me nuts too. I am mysql when I try this, and yes mysqld has been stoppped [mysql@localhost transactions]$ isamchk -e -a -i -o orders_history isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'orders_history' is not fixed because of errors User time 0.00, System time 0.01 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 142, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 1

fenway, yes I have made copies of all the files Here's the output from a few variations of isamchk: [mysql@localhost transactions]$ isamchk -f -r orders_history isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'orders_history' is not fixed because of errors [mysql@localhost transactions]$ isamchk -f -q orders_history Checking ISAM file: orders_history Data records: 20877511 Deleted blocks: 0 - check file-size isamchk: warning: Size of datafile is: 49 Should be: 2734016830 - check delete-chain - check index reference - check data record references index: 1 isamchk: error: Found key at page 1024 that points to record outside datafile isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'orders_history' is not fixed because of errors [mysql@localhost transactions]$ isamchk -o orders_history isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'orders_history' is not fixed because of errors [mysql@localhost transactions]$ [mysql@localhost transactions]$ ls -l orders_history.* -rw-rw-rw- 1 mysql mysql 9782 Jan 3 03:03 orders_history.frm -rw-rw-rw- 1 mysql mysql 4294967345 Jan 3 03:12 orders_history.ISD -rw-rw-rw- 1 mysql mysql 541793280 Jan 3 03:14 orders_history.ISM [mysql@localhost transactions]$ id uid=500(mysql) gid=500(mysql) groups=500(mysql) context=root:system_r:unconfined_t:SystemLow-SystemHigh [mysql@localhost transactions]$

my tmpdir has 22G, and the ISD file is 4G

How about: select filename, max(path), max(revision), max(id) from repos group by filename And, you might want to consider adding path to the group by (as fenway suggested). It's hard to tell from your data sample if filename is always 1 to 1 to path. Can you have the same filename in 2 diff paths ? If so, add path to the group by. Actually, probably better to do it regardless

I can't test this, but I believe this should do it: select filename, max(path), max(revision) from repos group by filename

How about: CREATE TABLE emailListDuplicate SELECT (minIid),name, email, storeStatus, storePhoto, storeAds FROM emailList GROUP BY id, name, email, storeStatus, storePhoto, storeAds