Hi,
This will do:
select name,status, max(_date) from dog
where status <(select max(status) from dog where status=dog.status) group
by name;
Thanks
ViSolve DB Team.
- Original Message -
From: "bruce" <[EMAIL PROTECTED]>
To:
Sent: Friday, January 05, 2007 10:23 AM
Subject: db/query
Hi,
Try using,
SELECT * INTO OUTFILE 'tmp/music_mp3.txt' FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY ' " '
LINES TERMINATED BY ' \n '
FROM mp3;
Thanks
ViSolve DB Team
- Original Message -
From: "Shain Lee" <[EMAIL PROTECTED]>
To: "MySql"
Sent: Friday, January 05, 2007 11:05 AM
S
-- Forwarded message --
From: Jake Peavy <[EMAIL PROTECTED]>
Date: Jan 4, 2007 10:48 PM
Subject: Re: mysql data into CSV file.
To: Shain Lee <[EMAIL PROTECTED]>
On 1/4/07, Shain Lee <[EMAIL PROTECTED]> wrote:
I wanted to get that info which has stored in relavent fields into a C
SELECT * FROM Dog GROUP BY FooId HAVING FooId = 1;
This should also work.
On 1/5/07, ViSolve DB Team <[EMAIL PROTECTED]> wrote:
Hi,
> dog
> fooID int
> size int
> id int
>
> dog
> fooIDsizeid
>1 2 1
>2 5 2
>1 5 3
>
> if i do a query
> sel
Hi
For example , suppose i have a database - Music, and a table in it is mp3.
under mp3 there is more fields , like, id, artist, name , mp3_file...etc.
I wanted to get that info which has stored in relavent fields into a CSV fle .
Can somebody help me to do that please ?
Can i do it mysql co
At 08:38 AM 1/4/2007, you wrote:
Hi,
I have seen that by default some tables are created as InnoDB and some as
MyISAM.
I guess the table type is not chosen randomly. How is it chosen the table
engine used?
And is InnoDB recommended now?
If you need transactions or RI.
Does it support f
hi...
a further test...
the following test tbl/information:
dog
name char
statusint
_date timestamp
idint
test data
dog
name status _dateid
tom 1 01/20/07 1
tom 2 01/21/07 2
sue 1
hi...
a further test...
the following test tbl/information:
dog
name char
statusint
_date timestamp
idint
test data
dog
name status _dateid
tom 1 01/20/07 1
tom 2 01/21/07 2
sue 1
Hi,
dog
fooID int
size int
id int
dog
fooIDsizeid
1 2 1
2 5 2
1 5 3
if i do a query
select * from dog where fooID='1';
i get
1,2,1
1,5,3
Try,
select * from dog where fooID=1 group by fooID;
- which retrieves the first instance;
resulti
Hi,
1. The table full error was due to temporary table size being large, which
is created during transaction processing. Try setting SQL_BIG_TABLES, to
avoid over-allocating memories for other queries.
2. Since Innodb engine, the innodb tablespace might run out of space. Try to
increase th
Chris White-4 wrote:
>
> Moving along... what I do first is SHOW
> CREATE TABLE `table_name`:
> ...snipped...
> So, first you remove the key:
> ALTER TABLE table_name DROP KEY `key_column`;
> then the foreign key:
> ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`;
> ...snipped...
hi...
continuing with my test (i was able to get the correct information
earlier.. thanks to all who helped!)
i have the following test tbl/information:
dog
name char
statusint
_date timestamp
idint
test data
dog
name status _dateid
tom
Duncan Hutty wrote:
I attempted to build 5.1.14-beta with ssl support and it failed
(output below).
It builds quite happily on this system without the ssl support
parameter to configure. Since it appears to fail in an area (timezone
system) that to me seems rather unrelated, I wondered if som
what do you consider a high number of updates/sec?
I'm the DBA for a popular website in that league (well, maybe not google or
yahoo but certainly ticketmaster) and we average ~210 DMLs/sec with peaks in
excess of 1,000. we use a mixture of myisam for static (or infrequently
updated) reference t
I attempted to build 5.1.14-beta with ssl support and it failed (output
below).
It builds quite happily on this system without the ssl support parameter
to configure. Since it appears to fail in an area (timezone system) that
to me seems rather unrelated, I wondered if something odd was afoot.
On 1/4/07, Daniel Kiss <[EMAIL PROTECTED]> wrote:
Hi All,
I'm designing a database where it is required for each row in a table to
have a unique identifier accross a bunch of running MySQL servers on a
network.
I'm considering two options:
1. Have a two-column primary key, where the first co
select s1.universityID, s1.actionID, ut.svn_dir_name (or other columns of
your choice)
from from SvnTBL as s1
inner join universityTBL ut on s1.univeristyID=ut.ID
left outer join
(select universityID from SvnTBL where actionID =3) as s2 ON
s1.universityID=s2.universityID
where s2.universi
This is structure of the table before I "fixed".
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`p
thanks for the derived tbl approach. it solved my 1st problem/issue.
the final query that i used is:
select distinct s1.universityID
from SvnTBL as s1
left outer join
(select universityID from SvnTBL where actionID =3) as s2 ON
s1.universityID=s2.universityID
where s2.universityID is nul
With so few rows, are you hitting the 50% rule? Try again with a decent
number of rows.
> -Original Message-
> From: devy [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 04, 2007 5:46 AM
> To: mysql@lists.mysql.com; [EMAIL PROTECTED]
> Subject: Fulltext problem
>
> Hi,
> today I've
Use a derived table (untested query):
select distinct universityID
from SvnTBL s1
left outer join
(select universityID from SvnTBL where actionID =3) as s2 ON
s1.universityID=s2.universityID
where s2.university ID is NULL
I'm not sure if derived tables are in all versions of MySQL, I
hi peter
i must be missing something. the following is my actual schema. i have a
test tbl with ~2900 rows... only a few of the rows have an actionID=3. each
universityID can have multiple actionIDs
mysql> describe SvnTBL;
+--+--+--+-++-
Bruce,
Try:
SELECT DISTINCT NAME
FROM DOG
WHERE STATUS != 3
Should do the trick.
You obviously don't want the STATUS field. If you include it, you'll
get more than one line per name. Similarly for ID. If you want to
include the STATUS or ID fields, then you obviously want more than one
Key 6 relates to the 6th key, or index, in the CREATE TABLE statement
for this table:
>> PRIMARY KEY (`prod_id`),
>> UNIQUE KEY `prod_no` (`prod_no`),
>> KEY `products_index1` (`prod_status`),
>> KEY `products_index2` (`prod_start_date`,`prod_end_date`),
>> KEY `on_sale` (`on_sale`),
hi chris...
your query,
>>SELECT name FROM dog WHERE status = 3 GROUP BY name<<
will actually give the items where status=3
however, i can't get the resulting issues by doing 'status!=3', because the
tbl has multiple status for a given name, so the query will still return the
other status that
bruce wrote:
i'm trying to figure out how to create a select query that groups
the tbl around 'name' such that if i want all names that do not
have a status=3, i'd get a single row for 'sue' and 'bob'
I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;`
wouldn't give you wha
hi...
i have the following test tbl
dog
name char
statusint
idint
test data
dog
name status id
tom 1 1
tom 2 2
sue 1 3
tom 3 4
sue 2 5
bob 1 6
i'm trying
Octavian,
1) You can use MyISAM for example when you use static information in a
webpage. For example, only for store information of customers, something
like that..
2) Innodb is a engine that support ACID, you can use for transactions. For
example, load information of sales from PDA ( field)
And is InnoDB recommended now?
It depends.. :)
Depends on... what?
I mean, if I don't need transactions, is there another reason for using
InnoDB?
If it is necessary I can build the client program without foreign keys
support also.
Thanks.
Octavian
--
MySQL General Mailing List
For li
Mungbeans wrote:
ALTER TABLE `mytable` DROP FOREIGN KEY `keyname`
#1025 - Error on rename of '.\database\mytable' to '.\database\#sql2-6ec-11'
(errno: 152)
ALTER TABLE `mytable` DROP INDEX `keyname`
#1025 - Error on rename of '.\database\#sql-6ec_13' to '.\database\mytable'
(errno: 150
Oh t
Hi Devy --
There are a couple of issues with your query below, and hopefully we can help
you figure it out.
First off, your table and query structure are fine. However, one can ask why
not use a TINYTEXT or even a TEXT field instead of VARCHAR(255). It's all in
the memory overhead. =)
Moving
InnoDB supports foreign keys, MyISAM does not.
MyISAM supports full text indices, InnoDB does not.
This is unfortunate. It has kept me using MyISAM where I'd rather use
InnoDB, although fortunately none of my applications are really hampered by
it.
The only work-around I can think of is to creat
On 2007-01-04 Octavian Rasnita wrote:
> I have seen that by default some tables are created as InnoDB and some as
> MyISAM.
>
> I guess the table type is not chosen randomly. How is it chosen the table
> engine used?
You can set a global and IIRC a database specific default for the database
typ
Hi,
I have seen that by default some tables are created as InnoDB and some as
MyISAM.
I guess the table type is not chosen randomly. How is it chosen the table
engine used?
And is InnoDB recommended now?
Does it support full text indexes? Or if not, is there a way of using full
text index
devy wrote:
---cut---
-
insert into ft_test (field1,field2,field3)
VALUES('mysql full text', 'this is a test', 'mysql fulltext');
-
the problem is that when I execute this query I always get 0 as
relevance:
---cut---
A FULLTEXT search will not match return val
Mungbeans,
I do not understand how you get error 152 from the ALTER.
./include/my_base.h:355:#define HA_ERR_ROW_IS_REFERENCED 152
Please print SHOW INNODB STATUS\G after you get that error.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign key
Hi All,
I'm designing a database where it is required for each row in a table to
have a unique identifier accross a bunch of running MySQL servers on a
network.
I'm considering two options:
1. Have a two-column primary key, where the first column is a normal
auto incremented ID field, the
Hi,
today I've experienced a problem that I don't understand and I can't solve!
I've created a table as follows:
---
CREATE TABLE `ft_test` (
`id` int(11) NOT NULL auto_increment,
`field1` varchar(255) NOT NULL ,
`field2` varchar(255) NOT NULL ,
`field3` text NOT NULL,
PRIMARY KEY (`i
38 matches
Mail list logo
