RE: select query question
create temporary table foo as select patientnumber, count(*) as rcount from source_table group by patientnumber having count(*) > 1; select count(*) from foo; -Original Message- From: Jon Rosenberg [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 02, 2001 1:29 PM To: [EMAIL PROTECTED] Subject: select query question I have a table where patient visits are logged each visit, the table is: patientnumber,visitdate,location I need to select and count the number of records that have 2 or more entries with the same patientnumber in sql-english: select count(*) where there are two or more records with the same patientnumber Any help with the SQL to do this would be much appreciated. Thanks! Jon - 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: Query speed
seems to me that the first query uses your primary key index. Since you have specified qualifications on crcid and tag in both aliases, it will resolve to a small number of rows in each alias table. The second query will join your aliases on the crcid index, and then the tag qualifications will resolve to a larger number of rows. A way to verify this is to run: select count(*) as rcount from server01_history a, server01_history b where a.day = b.day and a.crcid = 24 and a.tag = 100 and b.crcid = 24 and b.tag = 104 ; and select count(*) as rcount from server01_history a, server01_history b where a.crcid = b.crcid and a.tag = 100 and b.tag = 104; I bet the latter rcount value is much greater than the former. Did you run explains on these queries? braxton -Original Message- From: Roger Karnouk [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 02, 2001 9:57 AM To: [EMAIL PROTECTED] Subject: Query speed I am trying to run two queries which seem to me should execute at abut the same speed. My table is setup as follows: day - number of days since 1970 crcid - a number between 0 and 24 tag - a number used to identify record type total - the value stored (the rest of the record is just to identify this value) the primary key is day,crcid and tag I also have and index on crcid and on day seperately Table contains about 1,000,000 records This query takes 0.02 sec to execute: select (a.day+4)%7 as dow,avg(b.total/a.total) as average from server01_history a, server01_history b where a.day = b.day and a.crcid = 24 and a.tag = 100 and b.crcid = 24 and b.tag = 104 group by dow; note: dow is "day of week" which is used to group This query takes 1min 47 sec to execute: select a.crcid, avg ( b.total/a.total ) as average from server01_history a, server01_history b where a.crcid = b.crcid and a.tag = 100 and b.tag = 104 group by a.crcid; Both queries are similar they both alias the same table in order to use two separate records. Does anyone know why one query is so much faster than the other, and what can I do to speed up the second query without slowing down the first. Roger Karnouk - 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: LAST_INSERT_ID returning 3 rows?
last_insert_id is a function. It will return a value for each row in the
table. You want to run:
select last_insert_id() as lid;
instead of selecting from a table.
-Original Message-
From: Graeme B. Davis [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 30, 2001 3:17 PM
To: [EMAIL PROTECTED]
Subject: LAST_INSERT_ID returning 3 rows?
mysql> INSERT INTO outages (status) VALUES ('Open');
mysql> SELECT LAST_INSERT_ID() AS lid FROM outages;
+-+
| lid |
+-+
| 101 |
| 101 |
| 101 |
+-+
3 rows in set (0.00 sec)
Why would MYSQL do this? Any ideas?
Thanks,
Graeme
p.s. DESCRIBE outages;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| id| int(10) unsigned | | PRI | NULL| auto_increment |
| router| varchar(25) | YES | | NULL||
| techticket| varchar(15) | YES | | NULL||
| rfoticket | varchar(15) | YES | | NULL||
| nocticket | varchar(15) | YES | | NULL||
| sitesaffected | int(6) | YES | | NULL||
| telconame | varchar(100) | YES | | NULL||
| telcoticket | varchar(100) | YES | | NULL||
| start | datetime | YES | | NULL||
| stop | datetime | YES | | NULL||
| updated | timestamp(14)| YES | | NULL||
| slasent | datetime | YES | | NULL||
| sla | varchar(10) | YES | | NULL||
| vmstart | datetime | YES | | NULL||
| vmstop| datetime | YES | | NULL||
| rfo | varchar(200) | YES | | NULL||
| status| varchar(15) | | | Open||
| interface | varchar(255) | YES | | NULL||
| comments | text | YES | | NULL||
+---+--+--+-+-++
-
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: Problems with MOD
isn't this the expected behavior?
(1008306000-988344000)/86400 = 231.04
select mod(231.04,7);
0
231.04 mod 7 is .04, rounded down to 0. 7*33=231
the mod function rounds the result - that's expected, right? Other than that
I don't see what the problem is.
braxton
-Original Message-
From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]]
Sent: Saturday, April 28, 2001 7:38 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Problems with MOD
Roger Ramirez writes:
> Why do I get the following results?
>
> mysql> select (1008306000-988344000)/86400/7,
> mod((1008306000-988344000)/86400,7.);
>
++--
> +
> | (1008306000-988344000)/86400/7 |
mod((1008306000-988344000)/86400,7.)
> |
>
++--
> +
> |33.0060 |
0
> |
>
++--
> +
> 1 row in set (0.00 sec)
>
> Shouldn't the value in Column 2 be some number other then 0?
>
> I'm running MySQL 3.23.27.
Looks like a bug.
Will investigate it.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: Newbie question - Hopefully not too stupid!
howzabout SELECT Table1.id, Table1.name, count(1/(Table2.offon = 'off')) AS NOFF FROM Table1 , Table2 WHERE Table1.id = Table2.id GROUP BY Table1.id, Table1.name HAVING NOFF=0; works for me: mysql> create table Table1 (id int, name varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> create table Table2 (id int, offon varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql> insert into Table1 values(1,'red'); Query OK, 1 row affected (0.01 sec) mysql> insert into Table1 values(2,'blue'); Query OK, 1 row affected (0.00 sec) mysql> insert into Table2 values(1,'off'); Query OK, 1 row affected (0.01 sec) mysql> insert into Table2 values(1,'on'); Query OK, 1 row affected (0.00 sec) mysql> insert into Table2 values(2,'on'); Query OK, 1 row affected (0.00 sec) mysql> SELECT Table1.id, Table1.name, count(1/(Table2.offon = 'off')) AS NOFF -> FROM Table1 , Table2 -> WHERE Table1.id = Table2.id -> GROUP BY Table1.id, Table1.name -> HAVING NOFF=0; +--+--+--+ | id | name | NOFF | +--+--+--+ |2 | blue |0 | +--+--+--+ 1 row in set (0.00 sec) -Original Message- From: David Block [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 26, 2001 6:39 PM To: Opec Kemp Cc: Davin Flatten; Mysql Mailing List Subject: RE: Newbie question - Hopefully not too stupid! On Fri, 27 Apr 2001, Opec Kemp wrote: > Hi, > Have you tried: > > SELECT Table1.* FROM Table1 , Table2 > WHERE Table1.id = Table2.id AND Table2.offon = 'on' That was my original idea as well, but that would return Red, where Davin wants no Red since there is a Table2 row with an off corresponding to Red. I couldn't figure out the next step either. Good question! > > > Hello all! > > > > Here is what I am trying to do. > > I am trying to find only records > > that all have the same value for a > > specific field in a related table. > > > > A simplified example: > > Table1 has 2 fields - id, name > > Table2 has 2 fields - id, offon > > > > Given these sets: > > > > Table1 > > id name > > --- > > 1 Red > > 2 Blue > > 3 Green > > > > Table2 > > id offon > > > > 1 off > > 1 on > > 2 on > > 2 on > > 3 off > > 3 off > > > > I want to be able to select > > only the Colors that have only > > ALL ons relating to them. I > > do not want a combination of > > off, off or on, off etc... > > > > The only way I have been able > > to accomplish this was to count > > the number of instances for each > > table and save them in temp tables > > and then compare the results. There > > must be a better way to do this! > > > > Also if anyone would like to recommend > > an SQL reference or tutororial I would > > love to hear about it. > > > > Thanks in advance. > > -Davin > > --- David Block [EMAIL PROTECTED] http://bioinfo.pbi.nrc.ca/dblock/wiki NRC Plant Biotechnology Institute Saskatoon, SK, Canada - 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: count(*) questions
I think this is an interesting question. Note the following: count(col1) will tell you the number of non-null occurrences of col1 col1=value will return 1 is col1=value, 0 if col1<>value 1/0 will return null thus, count(1/(col1=value)) will tell you the number of occurences of value in col1. therefore, if you have three conditions on col1: value1, value2, value3, you can run: select count(1/(col1=value1)) C1, count(1/(col1=value2)) C2, count(1/(col1=value3)) C3 from table where col1 in (value1,value2,value3); does this help? braxton -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cindy Sent: Thursday, April 26, 2001 4:02 PM To: [EMAIL PROTECTED] Subject: count(*) questions OK, I have a perl script using mysql that pulls up a bunch of numbers for a table. Problem is it's taking a long time because of the number of select calls I'm making. Basically, I have a sequence of calls of the form: SELECT COUNT(*) FROM WHERE (execute, get value of count, print out as cell item in table) for each column in the row. My question: Is there some way to consolidate all the calls in each row into one select statment? Ie, SELECT COUNT(*), COUNT(*), COUNT(*) FROM WHERE Then I'd do one call per row for a total of row queries, rather than for rowXcolumns queries. I'm thinking I might be able to do this with some form of aliasing, but I'm kind of stumped on how to specify which set of conditions goes to which count. --Cindy -- [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 - 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: Can anyone help with a search?
if you are using tcsh: set docroot='path to your html root directory' find $docroot -name "*.html"> allhtml.list foreach FILE (`cat allhtml.list`) echo $FILE >> outhtml.list grep 'link' $FILE >> outhtml.list end you can get more sophisticated than this, but this will work. then look through outhtml.list, which will show all html files and the lines in those files matching your link. alternatively, you can do it in one line: grep 'link' `find $docroot -name "*.html"`> outhtml.list so long as you don't have too many html files. -Original Message- From: Alan Halls [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 24, 2001 1:02 PM To: [EMAIL PROTECTED] Subject: Can anyone help with a search? Hi, Ok, it is a little off the Mysql topic but I need to know something and I know someone out there can help. I need to do a search on a UNIX box using either egrep or another command that will search for all occurances of a link. we have updated part of our website and want to make sure there are no dead links, a worthy goal don't you think. We need to do a text-based search that includes sub-directories. Anyone know the command? Alan Halls Adoption.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 - 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: min () - sql troubles
the name you are getting is random. you want: create temporary table t1 as select distance, min(speed) fastest_speed from table1 group by distance; select table1.name, table1.distance, table1.speed from table1, t1 where table1.distance=t1.distance and table1.speed=t1.fastest_speed; P.S. I think it is not so great that mysql lets you have columns in the select clause that are neither in the group by nor aggregated. It's confusing. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Christian Wix Sent: Monday, April 23, 2001 6:02 PM To: [EMAIL PROTECTED] Subject: min () - sql troubles Hi SQL troubles: I have a table containing 4 columns: "name" - string, "distance"- double, "speed" - time and "id" - AUTO_INCREMENT (Primery key) I want a list of the fastest (speed) runner (name) of each distance and the speed. I use: select name, distance, min(speed) from table1 group by distance; I get a list where the speed and the distyance are always correct but the name is not always. Why is that? What should I do? Thanks, // Chris - Copenhagen - 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: Generic questions
Andrzej, As I understand it, MySQL does not have the concept of tablespaces like in Oracle. Databases are subdirectories underneath the /var/lib/mysql directory and files are tables within those subdirectories, so you can put an entire database or individual files on a different disk by creating a folder on the other disk with the proper permissions and then linking to that folder from /var/lib/mysql as detailed in the manual: http://www.mysql.com/doc/S/y/Symbolic_links.html The InnoDB table type has the concept of tablespaces: 'InnoDB stores its tables and indexes in a tablespace, which may consist of several files. ' from http://www.mysql.com/doc/I/n/InnoDB_overview.html for MyISAM tables, in terms of limits, each table consists of three files: one for the table definition, one for the data, and one for the indexes. Thus neither the data file nor the index can be larger than your OS supports, which is 2GB on linux AFAIK. In my experience working with mysql and oracle, the same table in mysql is much smaller than it would be on oracle, often by a factor of five or more. I'm not sure that others see this ratio of table sizes. braxton -Original Message- From: Andrzej Janczyszyn [mailto:[EMAIL PROTECTED]] Sent: Sunday, April 22, 2001 11:46 PM To: [EMAIL PROTECTED] Subject: Generic questions Where may I find more detail information about MySQL DBA? How MySQL handle very large table (>10,000,000 records)? How can I give direction where store data files? example: /var/lib/mysql | MYDATABASE | SPACE1 (/dev/sda3) | SPACE2 (/dev/sdb1) | --- SPACE3 (/dev/sdc2) Is it possible specify size and location of designated data files for a specific tables (like in Oracle create a table space and give a location of datafiles)? What are MySQL limits? All the questions relate to MySQL on Linux. Thanks, AMJ - 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: Referer Count
why are you using distinct(ref1)? also count(*) is ambiguous in this case. should be count(a.*). you have a ref1 column in both tables, but you don't join them on it. that's kind of confusing but won't cause the problem. member_id is the primary key on the members table, right? Otherwise you could get bad data. I think eliminating the distinct will solve your problem: select a.id, count(a.*) as nrows, b.field1, b.field2, etc. from a, b where a.id=b.id group by a.id, b.field1, b.field2, etc. order by nrows desc limit 100 note you can put a.id alone in the group by, but this is not ANSI sql, so it might be best to avoid this mysql-specific behavior. braxton -Original Message- From: Daren Cotter [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 22, 2001 5:31 PM To: [EMAIL PROTECTED] Subject: Referer Count I have a table, which keeps track of member information (including which member referred the member). To get a count of the # of referrals for member 25, my query is: SELECT COUNT(*) FROM members WHERE ref1 = 25; To get a list of the top referers and the # of referrals they have, my query is: SELECT DISTINCT(ref1) AS member_id, COUNT(*) AS count FROM members GROUP BY ref1 ORDER BY count DESC LIMIT 100 However, what I need, is a list of the top referers, along with their member information...name, email, password, etc. I tried using the following query, as I read about it in the MySQL manual, but it doesn't work: SELECT DISTINCT(a.ref1) AS member_id, count(*) AS count, b.password, concat(UCASE(SUBSTRING(b.first_name,1,1)), LCASE(SUBSTRING(b.first_name,2,LENGTH(b.first_name AS name, b.email, b.html_mail, b.ref1, DATE_FORMAT(b.signup_date, '%b %e, %Y') AS signup_date FROM members AS a, members AS b WHERE a.active_member = 'Y' AND a.ref1 = b.member_id GROUP BY a.ref1 ORDER BY count DESC LIMIT 10 This gives me correct info for the distinct a.ref1 and count fields, and produces data for the rest of the fields, but it is not actually that member's data. Is this possible to do with one query? If I want to get the top 100 referers' data, I don't want to do 100 separate queries. Please help! Thanks, Daren Cotter - 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: Mysql weird problem
I think your problem is at the netscape<>apache level, not the mysql level. Can you view static html pages served by your local apache with netscape? since netscape, opera, and IE all connect to apache, and then apache calls perl to connect to mysql, I doubt your problem has anything to do with perl or mysql. -Original Message- From: Haris [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 21, 2001 6:11 PM To: [EMAIL PROTECTED] Subject: Mysql weird problem Hello, When i transfer the project on my protable running Win98,Opera and IE work but netscape just tries continuouosly to connect but nothing. - 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: max FULLTEXT index size?
Sergei, I wanted to say thanks so much for your help with this. I went through the bitkeeper install, downloaded mysql 4.0, compiled under solaris 2.7, and the process of creating the big index that took days under 3.23 took 15 minutes. So thanks very much! Obviously I might have tweaked some things under 3.23 and made processing slightly faster, but this is a huge improvement, and makes the approach I was hoping to use feasible. thanks again, braxton -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 19, 2001 5:10 PM To: Braxton Robbason Cc: Mysql Subject: Re: max FULLTEXT index size? Hi! On Apr 19, Braxton Robbason wrote: > Hi all, > > I'm creating a fulltext index on a 400MB table, and the creation process > gets very slow. It's fast for the first 50MB of the index, and then it > grinds to what seemed like a halt after days. My question is - is there > anyone who's created fulltext indexes on this much data who can assure me > that it's possible? > > Merge tables are not an option because you can't query a merge table on a > fulltext index. > > thanks, > Braxton > Yes, FULLTEXT index works ok for 400 MB tables. In my tests FULLTEXT index was created (with CREATE INDEX) over 400 MB table in 15 minutes with new MySQL-4.0 code (not yet available, sorry). Everything works with 3.23 code too, but index creation is 50-100 times slower (and resulting index is ~10% bigger). Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ - 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: complicated query
you want a function that prepends the letter a to the category/subcategory
names that are not other. then you order by that function, but do not
display it.
select category,subcategory from foo2
order by if(category=
'other','zz',concat('a',category));
i.e. everything except other begins with an a as far as the order by is
concerned, while other is zz for sorting purposes.
-Original Message-
From: Jeff Shipman - SysProg [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 20, 2001 2:39 PM
To: [EMAIL PROTECTED]
Subject: complicated query
I would like to do something similar to an ORDER BY
in one of my select statements, but I'mt not sure
how to do something as complicated as this:
I have two columns, category and subcategory, that
I am retrieving. I would like category and subcategory
to be sorted alphabetically. This is easy with an
'ORDER BY 1 2', but I would like categories that
are named 'other' to be put off until the end. So,
I'd get something like this:
abcd
ghikj
z
other
Is there a way to do this type of query? Thanks in
advance.
Jeff Shipman E-Mail: [EMAIL PROTECTED]
Systems Programmer Phone: (505) 835-5748
NMIMT Computer Center http://www.nmt.edu/~jeff
-
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: Run time version of MySQL (Detail)
John, I don't think anyone can say for sure which is 'better'. MySQL is used in a different kind of environment than Oracle. Oracle is designed for an enterprise environment, where many different applications are used simultaneously to add or retrieve data from a central data store. MySQL is designed more for web applications, where data is maintained and accessed through a smaller set of programs. Many people have set up very successful MySQL web applications. It is easier and cheaper to administer mysql, and it is faster, but less external software is designed for it. For example, if you might use an external business intelligence tool, it will work with Oracle, but it might not work with MySQL, even through ODBC. In my mind, the two main problems with MySQL are: 1) lack of derived tables 2) lack of subqueries I'm in the process of switching from Oracle to MySQL. The main reasons for doing this were: 1) price. 2) administration. I can do DBA stuff on Oracle, but it's much easier and faster to do it on MySQL. Things that take a long time and significant study on oracle don't even require you to think about them on mysql. 3) hardware. Given hardware can handle much more data much faster on MySQL. 4) support. Oracle has a vested interest in keeping discussion of its product hidden inside proprietary tech support databases. Thus problems in the product are not obvious until you hit them. I didn't want to buy oracle support (they told us it started at $10k). 5) focus. Where is Oracle heading? Towards selling services and other applications to enterprise customers. They don't want small customers, and they don't want to make the product easier to use. They sell the product to conservative managers who are willing to spend extra money to ensure they have something that will do what they want, and Oracle will do most things if you spend enough money on consultants and hardware. hope this helps. braxton -Original Message- From: johnd [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 19, 2001 5:02 PM To: [EMAIL PROTECTED] Subject: Run time version of MySQL (Detail) Hi, We will delevope web based applications to put the databases on MySQL instead of Oracle. We will have plateforms such as: 1). IBM desktop 400 mhz processor 256 MB RAM 6 GB plus 20 GB hard drives Win2000 Server installed; or 2). Digital UNIX 500 mhz Alpha processor 1 GB RAM 35 GB RAID 5 with UNIX 4.0f installed The cost is so good for MySQL. What about the performace during working load web application databases? Could you tell us about your opinions or any of your information? Thanks for your information. Regards, John Ding - 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: Correct syntax?
select word, count(word) as total from search_words where word is not null group by word order by total desc limit 20 -Original Message- From: Graham Nichols [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 19, 2001 1:36 PM To: [EMAIL PROTECTED] Subject: Correct syntax? Thanks for help from this group I'm using "select word, count(word) as total from search_words group by word order by total desc limit 20" to locate the top 20 words in a database of website search engine words. The database had been altered to include an extra column, other than the 'word' column. This means that 'word' can now have a null entry sometimes. How can I modify the sql query above to exclude any row(s) in 'word' with 'NULL' please? regards, Graham l - 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
max FULLTEXT index size?
Hi all, I'm creating a fulltext index on a 400MB table, and the creation process gets very slow. It's fast for the first 50MB of the index, and then it grinds to what seemed like a halt after days. My question is - is there anyone who's created fulltext indexes on this much data who can assure me that it's possible? Merge tables are not an option because you can't query a merge table on a fulltext index. thanks, Braxton - 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 to structure a random query
http://www.mysql.com/doc/M/a/Mathematical_functions.html describes how to do this using the RAND() function. -Original Message- From: Alec Smith [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 18, 2001 6:27 PM To: [EMAIL PROTECTED] Subject: How to structure a random query I'm just starting to learn SQL and MySQL, and am curious as to how the following query might be built. I've got a table of X rows, each with a unique ID as determined by auto_increment when the row is inserted into the database. How would I go about doing a SELECT on a row of the database and have MySQL return a row at random? I figure there must be an easier way then doing a SELECT count(*) from tablename, using that in a random generator in Perl/PHP, and then looking up the resulting row with a second MySQL query. Thanks, Alec - 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: Phrase based fulltext searching
I thought I read that was in the plan for 4.0. I can't find where that's stated in the docs though - all I see is the stuff about boolean operators. Does anyone have experience with using the AOL PLS package for text searching? I am thinking of using it. Braxton -Original Message- From: Philip Mak [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 18, 2001 5:39 PM To: [EMAIL PROTECTED] Subject: Phrase based fulltext searching I read through the MySQL documentation on full text indexing, and there does not seem to be a way to search for a *phrase*, e.g. searching for a document that contains "Sailor Moon", as opposed to one that contains the word "Sailor" and the word "Moon", not necessarily together. (Unless I use LIKE "%Sailor Moon%", but that's inefficient...) Is the above statement correct? What techniques do people here use to overcome that limitation? (Using a program separate from MySQL for indexing text, perhaps?) -Philip Mak ([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 - 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
