Re: display days between two dates?
Hello, I think Mr. Carlson requires to output all the dates between two given dates. This can be done with defined variables and an auxiliary table, the requirement being that it should have at least as many rows as there are days between the dates and to directly give the number of days between the dates in the limit clause. (Hope in the future mysql will accept variables in the limit clause and also in IN clause). Select @stdate := '2002-02-07'; Select @stdate := @stdate + interval 1 day from tablename limit 5; Anvar. At 03:56 PM 06/05/2002 +0300, you wrote: Hello, Tim Carlson wrote: Hello, Newbie SQL person here. I am hoping to be able to do the following. Given two dates, I would like to display all of the days between them. So if I had the dates 2002-02-08 and 2002-02-12, I would like to have MySQL spit back 2002-02-08 2002-02-09 2002-02-10 2002-02-11 2002-02-12 Any way I can do this directly in MySQL without constructing a table? I've looked through the date maniplulation routines in the manual, but nothing jumps out at me. Any pointers would be appreciated. Suppose you have some table with column called date_col DATE. i.e. containing dates as above Now if your question is: How to select all rows where is true that date_col is between dates 2002-02-08 and 2002-02-12? You can use almost same spelling. (SQL is written to be close to natural english) mysql SELECT date_col FROM your_table WHERE date_col BETWEEN '2002-02-08' AND '2002-02-12'; will be valid statement :) -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - 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: dupe records
Hi, At 07:56 AM 03/05/2002 -0500, you wrote: [snip] Here is probably an easy question to answer, but I can't figure an EASY way to do it. Right now, I use a temp table with a unique column to solve it. I am hoping that there is a way in the SELECT statement. AnywayWhat I want to do is to select records from a table but if there is more than one with the same cont_id (that is the name of the column), that it would only select the first one of that id and skip the rest of that id, but continue with the other rows. I sure hope that makes since. Only 2 hours of sleep is not good before asking for help. [/snip] This query; SELECT cont_id, this, that, theother FROM tblFOO GROUP BY cont_id HAVING count(*) = 1 will select all records that appear only once in a table according to the GROUP BY condition. Sure it will. But the original post requires one row for each cont_id even if there are multiple rows with the cont_id. I feel eliminating the HAVING clause from the query would be the exact solution. SELECT cont_id, this, that, theother FROM tblFOO GROUP BY cont_id Anvar. This query; SELECT cont_id, this, that, theother FROM tblFOO GROUP BY cont_id HAVING count(*) 1 will select all records that are duplicates according to the GROUP BY condition 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: query problem
Hi, This might work for you: select @tempvar := max(datecolumn) from tablename group by datecolumn order by datecolumn desc limit 3; select * from tablename where datecol = @tempvar order by datecolumn desc; Anvar. At 06:12 AM 02/05/2002 +, you wrote: hi everyboby, How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. query,database,sql Thanks in advance. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - 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 problem
Hi, This might work for you: select @tempvar := max(datecolumn) from tablename group by datecolumn order by datecolumn desc limit 3; select * from tablename where datecol = @tempvar order by datecolumn desc; Anvar. At 06:12 AM 02/05/2002 +, you wrote: hi everyboby, How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. query,database,sql Thanks in advance. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - 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: delta between rows?
Hi, There comes to mind another solution or have missed something? set @prev_val := 0.0; Select colA-@prev_val, @prev_val := colA from tablename; Even adding an order by clause does not cause problem, Contrary to my belief that determining values for caluclated columns happen before ordering. Select colA-@prev_val, @prev_val := colA from tablename Order by Key1; works as well; Anvar. At 11:04 PM 29/04/2002 -0500, you wrote: If you have a primary key which is an autoincrement field then the following works. CREATE TABLE `tab1` ( Key1 smallint(5) unsigned NOT NULL auto_increment, ColA float default NULL, PRIMARY KEY (`Key1`) ) TYPE=MyISAM; insert into tab1 values (1, 3.4), (2,4.6), (3, 3.1), (4,8.2), (5,6.4); Select * from tab1; mysql Select * from tab1; +--+--+ | Key1 | ColA | +--+--+ |1 | 3.4 | |2 | 4.6 | |3 | 3.1 | |4 | 8.2 | |5 | 6.4 | +--+--+ 5 rows in set (0.00 sec) create temporary table t1 select Key1+1 as prev, colA from tab1 order by Key1; select key1, (tab1.colA - t1.colA) as Delta from tab1 inner join t1 on (tab1.key1 = t1.prev) order by key1; +--+---+ | key1 | Delta | +--+---+ |2 | 1.1980926514 | |3 | -1.5 | |4 | 5.0990463257 | |5 | -1.7971389771 | +--+---+ 4 rows in set (0.00 sec) Gordon Bruce A US MySQL Training Partner -Original Message- From: Nissim Lugasy [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 24, 2002 2:57 PM To: [EMAIL PROTECTED] Subject: delta between rows? To Mysql Team how can I generate a list of deltas between columns in different rows for the entire table? what I need is an sql command that does something like this: for N =0 to i do : select colA of current rowN - colA of pervious row(N-1) from tab1; ColA = floating point number. 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 mysql-unsubscribe- [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 - 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: Uppercase first letter of each word
Hi,
A function like initcap in other databases and programming languages in
mysql would be
much desired in such situations.
You may try this ugly code.
select ltrim(replace(replace(replace...(concat(' ',lower(strcolumn)),' a','
A'),' b',' B' ),' c',' C'), ...,' z',' Z')) from tablename;
Anvar.
At 11:10 PM 25/04/2002 -0500, you wrote:
I have no problem with figuring out how to punctuate a field that contains a
single word, but how would I code a SQL query to proper-case each word in a
field when the field contains, for instance, a title? I want to convert,
HOW NOW BROWN COW to, How Now Brown Cow
Thanks,
Doug
-
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 do i applying an equation to every row of a result set?
Hi, This might work for you but with two quries: SELECT @minval := least(min(colOne),min(colTwo)) FROM myTable; SELECT colOne-@minval FROM myTable; Anvar. At 06:26 PM 19/04/2002 -0400, you wrote: I am trying to normalize a data set based on the minimum values of certain columns. I figured out that I can get the minimum value using a query like SELECT least(min(colOne),min(colTwo)) FROM myTable Is there a way I can do the normalization in a single query? Ideally, something like... SELECT colOne-least(min(colOne),min(colTwo)) FROM myTable I realize that that does not work and maybe I need some kind of join. Is it possible to apply that subtraction to every row of the result set when it is returned? (instead of normalizing after I retrieve the result) 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 - 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: again date question
Hi, 1. date_format(datecolumn,'%b'); 2. date_format(datecolumn,'%d)+0; or if you want a string result trim(leading '0' from date_format(datecolumn,'%d)); The manual has very clear description of date functions. Anvar. At 01:47 AM 05/04/2002 +, you wrote: hi, I have some more problem with date query. 1.how to return month from numeric to aalpabet.04-Apr. 2.how to return day or month without 0 in front. i mean if day is 01-1,same also month if 02-2. Is it possible.please help me. Thanks in advance. - 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: Why does my delete not work :(
Hi sunny,
Yes it is a sad scenario. But hopefully this feature would be available in
the near future.
If you cannot use a programming language the only way AFAIK is to use
temporary tables.
Create temporary table temptable SELECT messages.*, if(main.topicid is
null,1,0) deleteflag
FROM messages
LEFT OUTER JOIN main
ON messages.topicid=main.topicid
WHERE main.topicid is null;
You have now the complete data of the original table but with an extra
column deleteflag
with either 1 or 0 as its value.
Now you can delete the rows of temptable by issuing the command
Delete from temptable where deleteflag = 1;
Alter table temptable drop deleteflag;
Delete from messages;
Insert into messages select * from temptable;
drop table temptable;
This is like taking food through nostrills but one without a mouth has no
better alternative
Regards.
Anvar.
At 12:17 AM 30/03/2002 +, you wrote:
So you're saying there's no actual DELETE statement for this? There is no
way in hell I can write any SQL to do that?? Thats sucks... :(
Thanks for the example, but how do I run it? I've only used PHP for taking
information out of a database and other simple MySQL queries so while your
Perl example kinda makes sense, I don't understand how to work it. Do I
just put that in a file and run open the file in a browser?
Thanks!
sunny
At 08:12 29/03/02 -0700, Rodney Broom wrote:
Good morning Sunny,
From: sunny [EMAIL PROTECTED]
...substituting SELECT with DELETE doesn't work :(
That's right, that's how MySQL works. And it doesn't support sub-queries
for this case, either. So you can't say:
delete from table where field in (select field from other_table)
I'd suggest doing this from another language, like Perl. For instance:
$list_list = $dbh-selectall_arrayref(qq{
SELECT messages.topicid
FROM messages
LEFT OUTER JOIN main
ON messages.topicid=main.topicid
WHERE main.topicid is null
});
for my $row ( @{$list_list} ) {
$dbh-do(qq{DELETE FROM messages WHERE topicid = $row-[0]});
}
Note, my example is rough, and not tuned for performance. But you get the
idea. Hollar if you still need a hand with this.
---
Rodney Broom
Programmer: Desert.Net
-
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
-
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: No support for multiple statements via JDBC?
Hi Allon, Only one query can be sent to server at a time. But your particular case can be handled with the single query Insert Into orders (Number) select Max(Number) FROM Orders; Anvar. At 04:45 PM 19/03/2002 -0800, you wrote: Hi All- We are using the mm driver for MySQL and multiple statements in one connection do not seem to be supported: I.e. SELECT @a:=MAX(Number) FROM Orders;insert into orders (Number) values (@a+1) The driver throws a syntax error. You can do either one of these statements on their own, and you can have a semicolon in the statement, but you cannot combine the statements. Are we missing something? How would you do a transaction or table lock without multi statement support? -Allon Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - 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: Re: No support for multiple statements via JDBC?
sql,Mysql Hi Allon, Sorry. It wont work. I didn't notice that you were inserting to the same table as the select. Anvar. - 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: sql help examining log table
Hi Viraj, You can do it using temporary table. Create temporary table tmp select subject from outgoing where auth='USER' order by timestamp desc limit 50; Select count(distinct subject) from tmp group by subject; drop table tmp; If the result of the second query is 1 all the last 50 messages have the same subject. It is assumed there are at least 50 rows for auth = 'USER'. Regards Anvar. At 03:34 PM 17/03/2002 -0500, you wrote: Hello, We use mysql to store outgoing email headers from our users and do throttling on users that appear to be spamming based on some simple queries to this table. We use the Communigate mail server and this throttling script is a PERL program implemented as a content filter. More information is here for those interested: http://www.cse.fau.edu/~valankar/ I am trying to figure out what is the best way to do a certain query. My outgoing log table looks like this: mysql desc outgoing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | rpath | varchar(80) | YES | | NULL| | | auth | varchar(80) | | MUL | | | | ip| varchar(80) | YES | | NULL| | | hfrom | varchar(80) | YES | | NULL| | | hto | varchar(80) | YES | | NULL| | | subject | varchar(80) | YES | | NULL| | | messageid | varchar(80) | YES | | NULL| | | timestamp | timestamp(14)| YES | | NULL| | | rcpts | smallint(5) unsigned | YES | | 0 | | +---+--+--+-+-+---+ What I would like to do is reject a message if the last 50 messages have the same subject. In other words, I want to look at the 50 latest entries in this table for a certain user (identified by the auth field) to find out if all of these messages have the same subject. The only way I can think of doing this is basically: select subject from outgoing where auth='USER' order by timestamp desc limit 50 And then going through each one of these rows in my program to see if they are all the same subject. Is there a way I can do this logic in the select query instead? Thanks, Viraj. - 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: Summarize
Hi, Select Sum(ifnull(column1,0)+ifnull(column2,0)+ ...+ifnull(columnn,0)) From tbl group by .. Anvar. At 11:34 PM 05/03/2002 +0100, you wrote: Dear, I am stuck on something, I would like to summarize multiple columns to a total value in a query and then display the output with php in a table. the summarize works for 1 column but not for two or more. any suggestions ? Thanks alot - 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: Help with Join
Hi David, Does this work for you? select customer.custcode, bookings.cust from customer left join bookings on customer.custcode=bookings.cust group by (customer.custcode) having max(ifnull(bookings.stdt,'-00-00') = '2002-02-16'; Regards, Anvar. At 09:06 PM 26/02/2002 +, you wrote: It's late, and I'm being dumb, so can anyone help ? I know that select customer.custcode, bookings.cust from customer left join bookings on customer.custcode=bookings.cust where bookings.cust is null; will show me all customers with no corresponding entry in bookings table. But I want to know those customers with no bookings in last few weeks : I thought maybe select customer.custcode, bookings.cust from customer left join bookings on customer.custcode=bookings.cust where bookings.stdt '2002-02-16' and bookings.cust is null but that aint right. Help ? Please ? David bot-bait 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: column1 like column2%
Hi, Try this: select * from table1,table2 where table1.column1 like concat(table2.column2,'%'); Anvar. At 04:57 PM 20/02/2002 +, you wrote: mysql,query Hi , is there any way of performing something like the below statement? select * from table1,table2 where table1.column1 like table2.column2 thanks in advance Rich - 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: Selecting records with the highest value no greater than x
Hi Benjamin, The perfect answer. Thank you, Anvar. At 08:44 AM 16/02/2002 +0100, you wrote: Hi. On Fri, Feb 15, 2002 at 09:05:02PM -0800, [EMAIL PROTECTED] wrote: In Re: Selecting records with the highest value no greater than x, [EMAIL PROTECTED] wrote: Hi Brent, I cannot think of a single query doing your job. IMO, there is one, if I did understand the question correctly: SELECT * FROM NEWS WHERE RATING = 4 ORDER BY RATING DESC, RAND() LIMIT 1; This give back a random news entry of the highest score available, but smaller than 5. But it can be done with two. SELECT @maxrating := MAX(RATING) FROM NEWS WHERE RATING = 4; SELECT * FROM NEWS WHERE RATING=@maxrating ORDER BY RAND() LIMIT 1; [...] Nice, assuming that the @maxrating is the syntax for a local variable within the server. Where is that discussed in the manual? They can be found under the term user variables: http://www.mysql.com/doc/V/a/Variables.html Some simple use of an API is the probably the best answer, it's probably ~ 25 lines of perl. [...] Depends on the needs. A pure SQL solution should be noticeably faster. Bye, Benjamin. -- [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: SQL Help, Please...
Hi,
At 11:21 AM 14/02/2002 -0500, you wrote:
On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
Hi
I am trying to write an auction script that uses mysql as its backend. Each
auction can have upto 25 sub auctions(cells) taking place.
I'm trying to query the DB to give me a list of all the successfull bids
for each cell, for each auction...
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as
b, sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ORDER BY bidTime DESC
This is further complicated in so much that multiple MAX bids may exist at
the same value, but, only the earliest should be returned for each cell.
Which is returning some of the columns correctly, namely auctionid, cellid
and bid, but it does not return the bidderId correctly.
Can anybody help?
Your query is simply NOT relationally correct... The database has NO way to
know WHICH bidder id to return in a given group. Suppose that for a given
auctionid and cellid there might be 12 different bidders. You are telling the
database engine to return ONE record for that group of 12 rows, so which
bidderid will it use? The correct behaviour would be for MySQL to reject the
query, it simply cannot be properly processed. Unfortunately I've found that
MySQL doesn't behave correctly in these cases, instead it just returns one of
the possible bidderid values at random.
This behaviour is well documented in the manual. There is a very practical
reason to allow this behaviour. Suppose for efficiency reasons data is
denormalized
and for example, id,name and some other particulars are all kept in the same
table . If the database was very strict that all the columns selected
should be
in the group by expression, one will have to put all these columns (id,name,..)
in the group by clause. Then the db engine will have to take all these
fields in
the intermediate ordering phase of the query execution. Surely this will be
inefficient in both time and space. But with the 'incorrect' behaviour of
Mysql
one can put all the data columns required to be returned in the select and do
group by only the id. This would make the query to complete very fast compared
to the former and the effect will be even more pronounced with index on id
field.
Your query would be technically correct if you used a summary function on
bidderid, like MAX(b.bidderId) or somesuch. The rule is that the returned
columns in a GROUP BY must either by mentioned in the GROUP BY section of the
query itself, OR they must be the results of a summary function. Any other
use is not correct for the reason stated above.
In other words, you need to rewrite your application logic. Most likely you
will need to add the b.bidderId to the GROUP BY and have the program walk
through the result set and do further sumarization on its own. Alternately
you might be able to craft an SQL statement that gets you what you want, but
without correlated subqueries it is going to be difficult or impossible. I've
had this same sort of problem myself...
Cheers
Carl
The problem can be solved by using temporary tables.
Create temporary table tmp1
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid FROM sa_bid as b,
sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ;
Create temporary table tmp2
Select t1.auctionid, t1.cellid, t1.bid, min(b.bidtime) as bidtime from tmp1
as t1, sa_bid as b
where (t1.auctionid = b.auctionid and t1.cellid = b.cellid and t1.bid =
b.bid)
group by t1.auctionid,t1.cellid,t1.bid
Select t2.*, b.bidderid from tmp2 as t2, sa_bid as b
where t2.auctionid = b.auctionid and t2.cellid = b.cellid and t2.bid =
b.bid and t2.bidtime = b.bidtime
I hope there may be better and simpler ways to achieve the objective.
Surely correlated subquery and derived table features might have been good
features for such
situations.
Anvar.
#
# Table structure for table `sa_auction`
#
CREATE TABLE sa_auction (
id int(11) NOT NULL auto_increment,
start datetime NOT NULL default '-00-00 00:00:00',
end datetime NOT NULL default '-00-00 00:00:00',
state enum('waiting','active','expired') NOT NULL default 'waiting',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_auction`
#
INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23
21:42:50', '2002-04-30 11:30:00', 'active'); INSERT INTO sa_auction (id,
start, end, state) VALUES (2, '2002-01-23 21:42:50', '2002-02-09 06:30:00',
'expired'); INSERT INTO sa_auction (id, start, end, state) VALUES (3,
'2002-03-23 21:42:50', '2002-07-05 09:00:00', 'waiting'); INSERT INTO
sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
'2002-08-01 11:30:00', 'waiting'); #
#
# Table structure for table `sa_bid`
#
CREATE TABLE sa_bid (
id int(11) NOT NULL
Re: outer join + count() + group by
Hi, Yes I have made some mistakes. There was problem with the outer join. It should have been ad outer joined to review, not the other way. I didn't notice the first tabel person in the query. Try the following with two tables ad and review and later add person table. We don't know the columns of person table to relate with the othere tables. select ad.id,sum(review.id is not null) from ad left join review on ad.id=review.id group by ad.id; OR select ad.id,sum(if (ifnull(review.id,0)=0,0,1)) from ad left join review on ad.id=review.id group by ad.id; the id column can't be null, it's defined as varchar(16) not null. Even though the id column is defined as not null and there is no null values in this field of review table, the value returned by the query for this column can be null for outer joins. In fact we use outer joins to return a row even if there is no rows in the second (right table in the outer join ) with all the columns of this row with null value. The above query (first) behaves like this: It will return one row each for each row in review table which has a corresponding id in ad table. Additionally it will return one row for each row in ad table for which there is no corresponding row in review table but with review column value as null. So for each id in ad for which there is no reviews the review.id will be null and the expression (review.id is not null) will return 0, for which there is review it will return 1. Hence summing this on this expression should give you the right answer. Anvar. - 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: Retrieving a numeric series from a SELECT
Hi, If you have a table with at least the number of rows that you want in your series you can do this. Set @Colnum := 0; Select @Colnum := @colnum+1 as colnum from tablename limit (number of rows required); set @column := 0; If you want a series starting from a value other than 1, initialize colnum value to one less than required starting value. Anvar. At 03:08 PM 08/02/2002 -0600, you wrote: Hi, everybody How can I get a numeric column from a SQL SELECT statement? I mean, with a SELECT get something like this ColNum or ColNum 1 10 2 11 3 12 4 13 5 14 6 15 ... ... Some time ago, somebody wrote a sample that do exactly what I need. In fact, I took that sample and saved it to my HD, but i can't find it, hehehe. TIA - 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: outer join + count() + group by
Hi, Does this work for you? select ad.id,adtype,name,sum(review.id is not null) from person review left join ad on ad.id=review.id group by ad.id; OR select ad.id,adtype,name,sum(if (ifnull(review.id,0)=0,0,1)) from person review left join ad on ad.id=review.id group by ad.id; Anvar. At 06:13 PM 11/02/2002 -0800, you wrote: i have what is basically a personal ad database. in one table, i have a list of advertisers. in another table i have a list of reviews. (there's a third table with stuff like their name, address, etc). i want to get a list of advertisers and the number of reviews all in one query. i can do it with an inner join just fine. select ad.id,adtype,name,count(1) from person,review,ad where ad.id=review.id and ad.id=person.id group by review.id; this only selects users who have reviews though (which i understand). i want to do it with an outer join. if a user has no reviews it should put a 0 in the count column. this query gets all the usernames whether or not they have reviews, but the count column is always 1. select ad.id,adtype,name,count(1) from person review left join ad on ad.id=review.id group by ad.id; what am i missing? -jsd- - 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: regarding mysql DATE support
Hi, Now(), sysdate(). Anvar. At 03:02 PM 07/02/2002 +0530, you wrote: Hai , is there any support of sys_date in mysql which is strong feature in oracle 8.x . i am using MYSQL 3.23 server version . With Warm Regards [EMAIL PROTECTED] JIN INformation Systems(P) Ltd Voice : 5275300/5097603/5097604/5281822 ext 218 www.jinis.com With Warm Regards [EMAIL PROTECTED] JIN INformation Systems(P) Ltd Voice : 5275300/5097603/5097604/5281822 ext 218 www.jinis.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 - 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: 1 billion row
Hi, An int datatype takes 4 bytes and a bigint 8 bytes. . Since there is an index created with bigint it will take an additional 8 bytes for the data and some more bytes for the pointer to the table rows. Thus a bare minimum of 20+ bytes is consumed corresponding to a row of data. A billion records will then need 20+GB of FREE disk space. The database may have many other overheads, for building indexes etc. You may also check the size of the operating system files for the table and index for the space consumed for 600M row. By dividing the total space by 600,000,000 will give you the space needed for a single row of data. What is the free disk space available before inserting the data? How do you commit the data? What is the table type? Anvar. At 10:05 AM 07/02/2002 +0200, you wrote: Hi, I have been trying to build a 1 billion row database, in mysql version 3.23.47, under Suse 7.3, but failed a several times, i reached around .600.000.000 rows... The table has an int(10) unsigned and a bigint(20) field, indexed by the bigint(20) field. The hardisk is 26GB SCSI, the computer is a dual Pentium III with 1GHz, with 2GB memory I'd like to have your opinion about what should I be carefull at the table creation or mysqld settings, and how fast will be the queries in this 1 billion row and if its worth to make all in 1 table. Thank you, Magyari Istvan - 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: regarding mysql DATE support
Hi Prabhu, Select cols from tbl where datecolumn date_add(datevar, interval 1 day); A look at Date and time functions of the manual would do good. But you originally asked about sys_date (is it not sysdate pseudocolumn). Regards, Anvar At 04:49 PM 07/02/2002 +0530, you wrote: i am asking regarding a query . select DATE from table name where DATE NEXT DAY(DATE). like this . could u write the query for me . - Original Message - From: Anvar Hussain K.M. [EMAIL PROTECTED] To: SNPrabhu [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, February 07, 2002 PM 04:29 Subject: Re: regarding mysql DATE support Hi, Now(), sysdate(). Anvar. At 03:02 PM 07/02/2002 +0530, you wrote: Hai , is there any support of sys_date in mysql which is strong feature in oracle 8.x . i am using MYSQL 3.23 server version . With Warm Regards [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: Retrieving a numeric series from a SELECT
Hi, If you have a table with at least the number of rows that you want in your series you can do this. Set @Colnum := 0; Select @Colnum := @colnum+1 as colnum from tablename limit (number of rows required); set @column := 0; If you want a series starting from a value other than 1, initialize colnum value to one less than required starting value. Anvar. At 03:08 PM 08/02/2002 -0600, you wrote: Hi, everybody How can I get a numeric column from a SQL SELECT statement? I mean, with a SELECT get something like this ColNum or ColNum 1 10 2 11 3 12 4 13 5 14 6 15 ... ... Some time ago, somebody wrote a sample that do exactly what I need. In fact, I took that sample and saved it to my HD, but i can't find it, hehehe. TIA - 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: regarding mysql DATE support
Hi, Now(), sysdate(). Anvar. At 03:02 PM 07/02/2002 +0530, you wrote: Hai , is there any support of sys_date in mysql which is strong feature in oracle 8.x . i am using MYSQL 3.23 server version . With Warm Regards [EMAIL PROTECTED] JIN INformation Systems(P) Ltd Voice : 5275300/5097603/5097604/5281822 ext 218 www.jinis.com With Warm Regards [EMAIL PROTECTED] JIN INformation Systems(P) Ltd Voice : 5275300/5097603/5097604/5281822 ext 218 www.jinis.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: 1 billion row
Hi, An int datatype takes 4 bytes and a bigint 8 bytes. . Since there is an index created with bigint it will take an additional 8 bytes for the data and some more bytes for the pointer to the table rows. Thus a bare minimum of 20+ bytes is consumed corresponding to a row of data. A billion records will then need 20+GB of FREE disk space. The database may have many other overheads, for building indexes etc. You may also check the size of the operating system files for the table and index for the space consumed for 600M row. By dividing the total space by 600,000,000 will give you the space needed for a single row of data. What is the free disk space available before inserting the data? How do you commit the data? What is the table type? Anvar. At 10:05 AM 07/02/2002 +0200, you wrote: Hi, I have been trying to build a 1 billion row database, in mysql version 3.23.47, under Suse 7.3, but failed a several times, i reached around .600.000.000 rows... The table has an int(10) unsigned and a bigint(20) field, indexed by the bigint(20) field. The hardisk is 26GB SCSI, the computer is a dual Pentium III with 1GHz, with 2GB memory I'd like to have your opinion about what should I be carefull at the table creation or mysqld settings, and how fast will be the queries in this 1 billion row and if its worth to make all in 1 table. Thank you, Magyari Istvan - 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: regarding mysql DATE support
Hi Prabhu, Select cols from tbl where datecolumn date_add(datevar, interval 1 day); A look at Date and time functions of the manual would do good. But you originally asked about sys_date (is it not sysdate pseudocolumn). Regards, Anvar At 04:49 PM 07/02/2002 +0530, you wrote: i am asking regarding a query . select DATE from table name where DATE NEXT DAY(DATE). like this . could u write the query for me . - Original Message - From: Anvar Hussain K.M. [EMAIL PROTECTED] To: SNPrabhu [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, February 07, 2002 PM 04:29 Subject: Re: regarding mysql DATE support Hi, Now(), sysdate(). Anvar. At 03:02 PM 07/02/2002 +0530, you wrote: Hai , is there any support of sys_date in mysql which is strong feature in oracle 8.x . i am using MYSQL 3.23 server version . With Warm Regards [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: Slow Inner Join Help
Hi Butch Bean,
What if you use a temporary table .
Issue the following queries.
Create temporary table tmptable as select sent_id,count(*) cnt
from tbl_sent group by sent_id;
Select sum(if(sent_id idvar,1,0)) as lessthan,
sum(if(sent_id idvar,1,0)) as greaterthan
from tmptable;
Let us know if it improves the performance?
Anvar.
At 11:43 AM 06/02/2002 -0500, you wrote:
I have a table with 2.9 mil records which represents 197k sentences stored
vertically. I do this because I need to know information about each word
and its relationship to other tables.
I want to know how many words have a particular word-group ID before and a
particular word-group ID after the word ID I am looking for. All of the
fields shown are indexed integer fields.
The examples below actually work fine but Word ID #8 happens to be a popular
word ('the' 216,000) and this query takes 1 min 15 sec to run. When the
Word ID I am looking for is not so popular it executes in 0.05 - 5.0
seconds. Basically, 90% of the time its OK but 10% of the time is taking
longer than the 90%.
One of the things I noticed with the server is that the CPU and disk access
is virtually nothing while these joins are running. Other parts of my
program can take the CPU near 100% when running multiple copies but when any
of those copies get to this Inner Join it is slow and is tagged as a MySQL
Slow query.
Are there settings I should be making on the Server?
Is there a better way or method to make a query like this go faster?
Thanks
Butch Bean
-
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 emulate subselect on larger table? ###
At 12:12 PM 28/01/2002 -0600, you wrote: I have 2 tables, detail and product. The detail table could have millions of rows. The product table could have a few hundred. I need to know which products are in the detail table. (It would also be nice to know which products aren't in the detail table). The problem is it has to be fast, very fast. Since it is on a web server it can't tie up the CPU for several seconds while it needlessly returns thousands of records. I only need to know which fields from one table *exist* in the other table. Sounds simple right? Ideally it would look like: select product_id from products where prod_id in (select prod_id from detail where somedetailwhere); The somedetailwhere is an optional where clause that could be applied to the detail table. It will use indexes so it will be quite fast. Now if I try a simple join like: select prod_id from products, detail where somedetailwhere and product.prod_id = detail.prod_id; it will of course return duplicate prod_id's because the product could appear in tens of thousands of detail items. I don't need to return thousands of rows. I only need to return 1 row of each prod_id if that prod_id appears in the detail table. I can't use: select distinct prod_id from products, detail where somedetailwhere and product.prod_id = detail.prod_id; because it takes too long. It will still returns hundreds of thousands of rows unnecessarily. It will return a maximum of the number of rows in products table (surely the database engine will have to work with more than 100 rows). So is there a solution to this simple problem? If you cannot even afford the overhead of determining the distinct prod_id rows the option is to create a new table with one colum for prod_id. When a row is added into detail a row will be inserted into this table if that id does not already exist and whenever a delete takes place you can delete the corresponding row of this table if no more rows exists in the detail table. Otherwise a column can be added to product table where you can keep a 0 for not present in detail or 1 for present in detail updating this column as and when necessary. TIA Brent Regards, Anvar - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order records ASC - but put sero fields las
Hi Anton, Try this: select State,City,Ifnull(Suburb,'') from tbl Order By State,City,Ifnull(Suburb,char(255)); Anvar. At 01:42 PM 23/01/2002 +0200, you wrote: database,sql,query,table Hallo All I have a select statement for 3 fields (State, City, Suburb) then I ORDER BY State,City,Suburb ASC The values gets sorted State -- City -- Suburb in Asc. order The problem is that where Suburb is zero, no value it gets placed at the top of the ordered list - I want any record where suburb happen to be zero placed at the bottom of the list and have it start from suburb a-z. Thus I will have : StateA1 - CityA1 - Suburba1 StateA1 - CityA1 - Suburba2 StateA1 - CityA1 - Suburba3 StateA1 - CityA1 - StateA1 - CityA1 - StateB1 - CityB1 - Suburbb1 StateB1 - CityB1 - StateB1 - CityC1 - Suburbc1 StateB1 - CityC1 - Suburbc2 StateB1 - CityC1 - IT STILL FOLLOWS THE State - City Order rules 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
Re: compound primary key
Hi, A compound index creates an index in the order specified. If key is (a,b,c,d) then the index is in the order a,b,cd. That is the index file keeps these columns ordered with a pointer to the actual row in the data table. Since b can have only two values, making it the first column in the index columns will not be efficient, but since b is many distinct values ordering on this key first makes sense. Also you are always querying with a in the where clause, it should be the first key. The second in the index order can be determined according to the data in the table and the retrieval requirement. If your query always uses a and b only then keep only these two in the compound index else I would suggest keeping the b column in the third or fourth position. Also the performance depends on the select statements you are issuing. The point to note is that in the index file the rows are kept in the order of the index keys. If a,c,d,b is the order of the keys and you want to search for some values of c mysql cannot use the index, but if you search of some value of a and then some value for c, it can use the index. A bit of reading on how the index works will greately enhance the quality of query one writes. regards Anvar. At 05:18 PM 21/01/2002 +0100, you wrote: Hi, I have some compound primary keys, all integer fields (a,b,c,d). In one table we have by mistake made the key (b,a,c,d). We allways use at least field a and field b in all lookups, and it seems to be working, but I wanted to ask: does it matter? Should we change the 'wrong' primary key to (a,b,c,d)? (Will mysql perform better/faster?) Also, I wonder what is the best order for my primary keys: a intThis is the 'main' id, very many distinct values b tinyintThis is a source code, possible values are 1 or 2 c smallint Some counter d tinyintAnother counter or b tinyintThis is a source code, possible values are 1 or 2 a intThis is the 'main' id, very many distinct values c smallint Some counter d tinyintAnother counter Which will perform faster, when all lookups use both a and b? -- Roger - 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: Help with sql syntax
Hi, Select person from persons as p, person_skills as ps where p.personid = ps.person_id And ps.skill_id in (id1,id2..); Anvar. At 09:40 PM 21/01/2002 -0500, you wrote: I have three tables as follows (simplified): persons (person_id,person) skills( skill_id,skill) person_skills( person_id,skill_id) A person can have one or more entries in the person_skills table, indicating their skills. I need to select persons where there skills match ALL skill_id's in a set, not just one match. I can do this with muliple selects and coding (using php), but I think there is a way to do it with straight sql. Can anyone help me with this. Brian - 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: Why does DISTINCT take so long time ??
Hi, Surely, the having clause is not redundant ( I misread it as 0). Is it not the compound index (on mot,date,numresponse) which would make the query fast instead of three single column indexes? Thanks, Anvar. At 01:40 PM 19/01/2002 -0500, you wrote: Anvar had some very good explanations about the time it takes to run the queries. ##Here are some work arounds: ##If you need to have these columns (mot, date, numresponse) in the group by clause, ##try putting an index on each of them to speed it up. mysqlalter table searchhardwarefr3 add index idx_mot(mot); ##... etc. ##this should speed up the 1st query for sure. ##if the second query is still slow, (i'm not sure about the exact details of mysql, so this might or might not make a difference) ## put the results from your first query into a temporary table (mytemp): mysqlcreate temporary table mytemp SELECT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count1 LIMIT 100; ## then explicitly index both columns mysql alter table mytemp add index idx_count(count); mysql alter table mytemp add index idx_numresponse(numresponse) ##and then run the following query mysqlselect distinct count, numresponse from temp (mytemp) ##by the way, I don't think the HAVING clause is redundant. Good Luck. - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why does DISTINCT take so long time ??
Hi, Yes, the reason for the time difference is that for distinct query, as Sinisa noted, it has to reiterate. For the output to generate, first the rows have to be ordered ( in this case since count(*) is given every column should be present in the comparison.) using a temp table (or any other mechanism to keep rows ordered). For the first query also there should be an intermediate temp table to order rows but here it is only necessary to consider the columns in the group by clause. Considering these, a rough and primitive estimate of the time taken to execute the query can be found. Suppose the average length of a row is 300 bytes and the three columns in the group by clause takes 30 bytes average. Then if the first query takes 15 minutes, the second query will take 150 minutes. This may not be the real scenario with mysql but some thing similar. The having clause I feel, is redundant. the 15 min for the first qurey seem too much, perhaps indexing might help. Hope somebody else has a better explanation. Anvar. At 02:41 AM 18/01/2002 +0100, you wrote: Hi, I've notice sometimes DISTINCT clause take a really high amount of time to remove duplicates whereas it should be really quick (I assume it should be ;)) My first query is : mysql SELECT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count1 LIMIT 100; it returns : +---++ | count | numreponse | +---++ | 2 | 111239 | | 2 | 108183 | | 2 | 73 | | 2 | 111383 | cut | 2 | 111239 | | 2 | 111760 | | 3 | 109166 | | 2 | 09 | | 3 | 109166 | +---++ 58 rows in set (14 min 51.15 sec) My second query is : mysql SELECT DISTINCT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count1 LIMIT 100; Well I'm not enough patient to wait, but when I stop the querie, it has been running for more than 3500 seconds... (and more than 45mn in 'Removing duplicates' state...) mysql EXPLAIN SELECT DISTINCT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count1 LIMIT 100; +---+---+---+-+-+--+ -+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra| +---+---+---+-+-+--+ -+--+ | searchhardwarefr3 | index | NULL | PRIMARY | 75 | NULL | 2026032 | Using index; Using temporary | +---+---+---+-+-+--+ -+--+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count1 LIMIT 100; +---+---+---+-+-+--+ -+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra| +---+---+---+-+-+--+ -+--+ | searchhardwarefr3 | index | NULL | PRIMARY | 75 | NULL | 2026032 | Using index; Using temporary | +---+---+---+-+-+--+ -+--+ 1 row in set (0.00 sec) Why does it take so much time to remove duplicates in only 58 rows ?? Thank you :) Regards, Jocelyn Fournier Presence-PC - 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: Curious result: Indexing numbers vs. strings
Hi, Since the equality test is for a number, the phone_no field of every row of the table is converted into a number first and tested for the equality. This makes it impossible to use the character index and so forces the full table scan. If it were using the index then, I think, it cannot find the intended records . This is because if it were the case, the number 0636941 which is in fact 636941 should be first conveted to string '636941' and the query executed and that is not the logical one to do. Anvar. At 09:46 PM 10/01/2002 +0100, you wrote: * Steve Rapaport I just found this unexpected result, those who know how indexing works might understand it but I don't, and it's funny: I have a large phone listing with over 22 million records. The phone number is a string (varchar 16). There's an index for the first 8 chars of the phone number. Now note the response times when I forget the quotes: mysql select rec_no,phone_no from White where phone_no='0636941'; +-+--+ | rec_no | phone_no | +-+--+ | 1860796 | 0636941 | +-+--+ 1 row in set (0.06 sec) mysql select rec_no,phone_no from White where phone_no=0636941; +-+--+ | rec_no | phone_no | +-+--+ | 1860796 | 0636941 | +-+--+ 1 row in set (2 min 47.01 sec) I would have expected the second query to either work quickly, or to fail altogether. I am curious how it succeeded, but failed to use the indexing. I suspect that any insight we get from this could help in optimizing db design and queries in future. I am guessing that the index is used, but that for each row the fetched varchar must be converted to integer, to compare it with the integer in the where clause. -- Roger - 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: Date Field + Time Field = Datetime Field?
Hi Alex, I don't think your problem will solved by making the time columns to full datetime columns as there is no functions to subtract two datetime values directly. But you can can keep the time columns and go on like this: convert the time into seconds using time_to_sec function. subtract fromtime from totime. this will yield the difference in seconds now you can take the elapsed time in hours,minutes or seconds. The sql might be something like: Select (time_to_sec(totime) - time_to_sec(fromtime)) / 60 / 60 as hours from table. Section 7.4.11 of the manual explains date and time functions. Anvar At 06:00 PM 10/01/2002 +, you wrote: I've been working with a timesheet database, where all the employees of my small business enter in the hours they work on projects. I've been storing, for each record, a date of work, a start time, and a finish time. When I attempted to write a Perl script to display invoices, though, I ran into the issue that subtracting one time from another yields inconsistent results. Thus, I'm going to convert to datetime fields, which would store just the start and finish times as datetime. I'd like to automate the switchover, so I don't have to go through and maually update. I thought at first that UPDATE time_worked SET dtstart = concat(datework, start); would work, but that gave me a syntax error. I can't seem to find anything in the manual that would help, either. Is there any way to do this without going through and manually updating? I'd really appreciate any hints you could give. FYI, I'm running MySQL 3.23.37. Thanks, Alex Kirk - 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 - Long Queries
Hi Bill, Try putting first all the county equality tests together and then the other tests in the where clause. select * from jobs where (county = 'county1',' or county = 'county2'... or description like '%county1%' or or description like '%county2' ... or title like '%county1%' or title like '%county2') . Since you are using perl the above query can be easily created. ie if user just selects county1 the query will be select * from jobs where (county = 'county1' or description like '%county1%' or or title like '%county1%' ) . if count1 and count3 are selected it becomes select * from jobs where (county = 'county1' or county = 'county3' or description like '%county1%' or description like '%county3' or title like '%county1%' or title like '%county3') . if no field selected by the user then just select * from jobs; These queries should run faster if you have index on county column. Since this column is small in size compared to the other two fields first searching on these should return the result faster. The description and title can be in any order in the where clause depending on the size of the fields and the probability of finding the string in these fields. If description column is smaller and there is higher probability of finding the string in this column then put this column in the where clause before the title column. If not, then first put the title and then the description. Tell us if there is any performance difference. Note that changing the query does not have any effect if user selects only one county. Anvar. At 03:45 PM 04/01/2002 -0500, you wrote: Greetings all, I'm new to the list, and I'm somewhat new to MySQL. I have a somewhat simple question that I hope someone can help me with. I'm designing a database for a job search. There are a little over 10,000 entries in the db. I need to query the database to accomplish a few results, one of which is a little perplexing to me. Just a little background as to how it works - The person can choose a county and a basic job description, and I make the query string dependant on what they choose. For example, if they choose one specific county or select all counties, it sets the query appropriately. The problem that I have is - if there are 7 counties, I've got the string saying: select * from jobs where (county='county1' or description like '%county1%' or title like '%county1%') or (county='county2' or description like '%county2%' or title like '%county2%') etc, and if they choose a specific keyword it then throws that into the query. The computer is a dual P3 1GZ w/1.5GB's of RAM - RedHat 7.2 - PERL DBI - it takes over 3 seconds to complete queries like that, and I know that I can get it faster. Any suggestions would be appreciated sincerely. Thanks, --Bill - 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: question on selects in multiple tables
Hi Cindy, SELECT Inventory.*, Customer.Name from Inventory Left Join Customer on Inventory.PurchasedBy = Customer.ID where Inventory.PurchasedBy = Custormer.ID; Your query is an equi-join query, which means that there should be data for only those rows for which the equality in the where clause hold. So it will not show any row in the result if there is no a corresponding ID in Customer table for the PurchasedBy column of the Inventory Table. Left Join clause forces the query to return a row even if there is no value in the second table. More information can be had from the fine manual or any book on sql. Anvar. At 11:12 PM 01/01/2002 -0800, you wrote: OK... let's say I have two tables. Let's say one is an inventory table, a bunch of items. One of the fields is for the inventory items that have been sold, and are keys to the second table, which is a list of customers; names addresses. So let's say I want to pull out all inventory items acquired in a particular month. Some of them have been sold, some have not. I want to list them all, and if they've been sold, the names of the purchasers, if not, then just blank. Problem is, of course, when I construct something like SELECT Inventory.*, Customer.Name WHERE Inventory.PurchasedBy = Customer.ID (etc) I get an ungodly mess for those items with no Customer.Name becasue of the way the query is joined(I think that's the right terminology) across the tables. Is there a way around that? This has got to be a pretty common scenario. I've tried WHERE Inventory.PurchasedBy = Customer.ID OR Inventory.PurchasedBy = 0, but that does not help. Thanks... --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: SubSelect Workaround help
Hi Paul, There is no direct way to make a string by concatinating strings of different columns. But your problem can be solved in a different way. select distinct p.symbol, i.name from portfolio p, portfolio pp, stockinfo i where pp.type = '401k' AND pp.owner = 'jim' and p.symbol = i.symbol and p.symbol = pp.symbol order by p.symbol; Hope it works for you Anvar At 02:29 PM 02/01/2002 +, you wrote: How would one load the results of the following query into a one line comma separated list so that it could be then included with an IN expr to get around the lack of subselect in MySQL select distinct symbol from portfolio where type='401k' AND owner='jim' select distinct p.symbol, i.name from portfolio p, stockinfo i where p.symbol = i.symbol and p.symbol in (string name) order by symbol Thanks. Happy New Year Paul _ 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 - 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: Changing data
Hi, I am puzzled not to see a funciton like initcap of oracle in mysql. Perhaps it is already there. Here is on (ugly) work around : Update table set name = replace(replace(replace(replace(replace(replace(replace(replace(replace( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace (replace(replace(replace(replace(replace(replace(concat(ucase(left(name,1)), lcase(substring(name,2))),' a',' A'),' b',' B'),' c', ' C'),' d', ' D'), ' e', ' E'),' f', ' F'),' g', ' G'),' h', ' H'),' i', ' I'),' j', ' J'),' k', ' K'), ' l', ' L'),' m', ' M'),' n', ' N'),' o', ' O'),' p', ' P'),' q', ' Q'),' r', ' R'), ' s', ' S'),' t', ' T'),' u', ' U'),' v', ' V'),' w', ' W'),' x', ' X'),' y', ' Y'), ' z', ' Z'); This will handle any number of words in name as well as any number of successive spaces. I am sorry if it seems too ugly. Anvar. At 08:03 PM 03/12/2001 -0800, you wrote: Okay, so if it won't work with more than one space, are thos records doomed to upper only or First letter capitalized? -Original Message- From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 11:10 AM To: 'Gill, Vern' Cc: '[EMAIL PROTECTED]' Subject: RE: Changing data Try the following 2 queries. UPDATE table SET name=concat(ucase(left(name,1)), lcase(SUBSTRING(name,2))); UPDATE table SET name=concat(left(name, INSTR(name, ' ')), ucase(mid(name,INSTR(name, ' ')+1,1)), substring(name FROM INSTR(name, ' ')+2)) WHERE INSTR(name, ' ') 0; The first query will capitalize the first letter of every entry and make the rest lower case. The second query will find the first blank, then capitalize the proceeding letter. Note that for names that have more than one space in them (PRO SPORTS TEAM), this won't work. But you can find those records that have more than one space in them by running THIS query: SELECT * FROM table WHERE name REGEXP .* .* .* - Jonathan -Original Message- From: Gill, Vern [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 10:45 AM To: 'Jonathan Hilgeman' Subject: RE: Changing data Cool. Thank everyone for help with that. Now, one more question; Can I use MySQL to change the data's case? I.E.; PROS TEAM - Pros Team PINEAPPLES - Pineapples RAPID PRINTING - Rapid Printing -Original Message- From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 8:27 AM To: 'Gill, Vern'; '[EMAIL PROTECTED]' Subject: RE: Changing data Try this query: UPDATE table SET phone = concat((,left(phone,3),) ,mid(phone,3,3),-,mid(phone,6,4)); It should convert 00 to (000) 000-. Take a look at this page if you want details on how it works: http://www.mysql.com/doc/S/t/String_functions.html - Jonathan -Original Message- From: Gill, Vern [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 3:23 AM To: '[EMAIL PROTECTED]' Subject: Changing data how would I change in all rows data that is; 00 to 000-000- or (000) 000- Can this even be done with mysql? Thank you in advance... Vern H. Gill State Director Director of Marketing Conejo Valley Jaycees http://www.conejovalleyjaycees.org - 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 - 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: Date Differance in MySQL
Hi, Select unix_timestamp(Now()) - unix_timestamp(datetime_column) from table_name; At 08:50 AM 29/11/2001 +0100, you wrote: Hi Could i in some way find out the seconds between to datetimes I.E. I have a DATETIME in a table and i want to find out how many second it is from that DATETIME to NOW() Does anyone have an idea? /Jonas - 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: LIMIT by column return
Hi Tom, I don't think there is a straight way in Mysql to accomplish this. It would have been great if Mysql included this provision. I suggest you to use temporary tables. First make a temporary table inserting values of job and the maximum date of inv for the job. Create temporary table temp1 select max(date) dt , job from tbl group by job; Now create another temp table with vaulues of job and maximum date but which is not in the temporary table created first. Outer join can be used here. Create temporary table temp2 select max(date) dt, job from tbl a left join temp1 b on a.job = b.job and a.date = b.dt where b.job is null group by job; Insert one temp into the other (insert into temp1 select * from temp2). Querying this table along with the original tables will yield the result. I hope there would be more elegant solution to this problem. Note : The date, job combination should be unique for this to work correctly. Anvar. At 09:22 AM 29/11/2001 -0800, you wrote: I'm trying to write a query that will limit the results but not by the total amount returned like a normal LIMIT. It's an invoice database and I would like to return the last to invoices for a certain job. So if there are 100 jobs with several invoice dates I would like to return a web page displaying a table of invoice dates ordered by job name and LIMIT the invoice dates to the last two invoice dates for each job. Here's my query so far; SELECT site_service.id, site_service.service_date, site_service.invoice_date, site.site_id, site.name, site.state, site.status FROM site_service, site WHERE site_service.site_id = site.id AND site.company = 'Nextel' If I add LIMIT 2, I only get two invoices. I'm trying to get the last to invoices for each site. Is this possible or is there another direction I should go? I checked out the MySQL site without any luck. Thanks, Tom - 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 date arithmatic
Hi Alec, Instead of adding 6 to current_time_stamp add 600. Hope the logic is clear. Anvar. At 09:40 PM 26/11/2001 -0500, you wrote: I've got a table containing dates as follows (number of rows edited for length) mysql select * from chat_schedule; +-+--+-+ | schedule_id | model_id | timeslot| +-+--+-+ | 1 |2 | 2001-11-08 21:00:00 | | 2 |2 | 2001-11-08 22:00:00 | | 3 |5 | 2001-11-09 16:00:00 | | 4 |5 | 2001-11-09 17:00:00 | | 5 |3 | 2001-11-09 18:00:00 | | 6 |3 | 2001-11-09 19:00:00 | | 7 |2 | 2001-11-09 21:00:00 | | 8 |2 | 2001-11-09 22:00:00 | | 9 |5 | 2001-11-09 23:00:00 | | 83 |7 | 2001-11-26 18:00:00 | | 85 |7 | 2001-11-26 19:00:00 | | 86 |1 | 2001-11-26 20:00:00 | | 87 |2 | 2001-11-26 22:00:00 | | 88 |2 | 2001-11-26 23:00:00 | | 89 |8 | 2001-11-27 18:00:00 | | 90 |8 | 2001-11-27 19:00:00 | | 91 |1 | 2001-11-27 20:00:00 | | 92 |2 | 2001-11-27 22:00:00 | | 93 |2 | 2001-11-27 23:00:00 | | 94 |7 | 2001-11-28 18:00:00 | | 95 |7 | 2001-11-28 19:00:00 | | 96 |1 | 2001-11-28 20:00:00 | | 97 |2 | 2001-11-28 22:00:00 | | 98 |2 | 2001-11-28 23:00:00 | | 99 |7 | 2001-11-29 18:00:00 | | 100 |7 | 2001-11-29 19:00:00 | | 101 |1 | 2001-11-29 20:00:00 | | 102 |2 | 2001-11-29 22:00:00 | | 103 |2 | 2001-11-29 23:00:00 | | 104 |7 | 2001-11-30 18:00:00 | | 107 |2 | 2001-11-30 22:00:00 | | 108 |2 | 2001-11-30 23:00:00 | | 109 |7 | 2001-11-30 19:00:00 | | 110 |1 | 2001-11-30 20:00:00 | | 111 |7 | 2001-12-05 17:00:00 | +-+--+-+ 104 rows in set (0.00 sec) When I run the query SELECT m.name,DATE_FORMAT(s.timeslot, '%W') as dayname, DATE_FORMAT(s.timeslot, '%d') as date, DATE_FORMAT(s.timeslot, '%H') as hour FROM models m, chat_schedule s WHERE m.model_id=s.model_id AND s.timeslot = CURRENT_TIMESTAMP() ORDER BY s.timeslot I get back the expected result -- all scheduled chats from today forward for as many times are currently scheduled (23 rows returned). However if I run SELECT m.name,DATE_FORMAT(s.timeslot, '%W') as dayname, DATE_FORMAT(s.timeslot, '%d') as date, DATE_FORMAT(s.timeslot, '%H') as hour FROM models m, chat_schedule s WHERE m.model_id=s.model_id AND s.timeslot = CURRENT_TIMESTAMP() AND s.timeslot = CURRENT_TIMESTAMP()+6 ORDER BY s.timeslot zero rows are returned. How can I go about modifying query #1 so that only chats scheduled for today and the next 6 days are returned (7 total days worth)? I know my problem is calculating the dates for the second AND clause, but I'm stumped as to where since the first portion works OK. Thanks in advance, 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: Selecting boundary dates for a given week using date/time functions?
Hi Chris, The following query should work for you. select date_sub(date_col, interval weekday(date_col) day), date_add(date_col, interval 6-weekday(date_col) day) from table_name Anvar. At 07:20 PM 25/11/2001 -0500, you wrote: Hey all, I'm hoping someone can help me out with a little date/time problem I'm having. I've got a database full of timestamps that I want to group together based on their week. I can use the WEEK() function or DATE_FORMAT() to convert the timestamp to a week number, but I'd rather display the boundary dates for the week because I don't feel the week number is very informative. e.g. Given the timestamp '2004095959' (a Wednesday), I would like to output '2001-11-12 to 2001-11-18' (which is Monday to Sunday). Is there a way using the date/time functions of MySQL to make this conversion in my SELECT statement? I haven't been able to figure out a way to get MySQL to use a week number to generate these boundary dates. I'm assuming the final SQL will look something like: SELECT CONCAT(function_to_get_monday, ' to ', function_to_get_sunday)... Any help is appreciated. -- coop - 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: many similar requests - 1 request
Hi Boris,
Try this:
Select ifnull(position1,0)+ifnull(position2,0) As slno, title from book
where ifnull(position1,0)+ifnull(position2,0) = 200 order by slno
Anvar.
At 09:49 PM 17/11/2001 +0100, you wrote:
hi fellow mysql users :)
i'm programming a little application, and i do the following :
for ($i=0 ; $i200 ; $i++)
{
$result=mysql_query(select $i, title from book where position1=$i \
or position2=$i); // this query returns only 1 row
$row=mysql_fetch_row($result);
print(position: $row[0] -- title: $row[1] \n);
}
this generates 200 sql queries... :/
do you see a way to make 1 sql query that would return my 200 rows ?
maybe something like :
select (increment j 0 - 200) , title from book where position1=j \
or position2=j;
note: given a number between 0 and 199, the number is stored in
position1 OR position2 , but never both.
any idea ?
--
Boris Hajduk [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: ORDERING A LEFT JOINED TABLE
Hi, At 05:35 PM 15/11/2001 -0800, you wrote: I'm having a problem sorting records that are used in a left join statement. Basically what happens is that any record that is not in table1 doesn't get sorted correctly. It first sorts all the records that have valid 'c2' records in both table1 and table2, then moves on to all the records that only have valid 'c2' records in table2. Is there any way to make the ORDER BY portion of the statement insert a '0' value for records that do not have a table1.c2 value. Here is the a simplified SQL statement that illustrates my point. SELECT * FROM table2 LEFT JOIN table1 USING (c1) ORDER BY (table2.c2 - table1.c2) If I read you right I feel you are looking for this qurey: SELECT * FROM table2 LEFT JOIN table1 USING (c1) ORDER BY (table2.c2 - ifnull(table1.c2,0)) Note Any mathematical operation with a null value yields null. Anvar. - 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: Need help with a query ...
Hi John Kelly, This is not a perfect solution but may be useful to you. But still with two queries! From Mysql prompt issue these two queries. SELECT @maxcat := max(Category) FROM table WHERE category = 'Sports:Football:Players' OR category = 'Sports:Football' OR category = 'Sports'; Select * from table where category = @maxcat; Note that abc is greater than ab This will not be affected by the depth of category. Anvar. At 06:15 PM 02/11/2001 -0800, you wrote: Hi, I have a MySQL table with a column that contains some of a web site directory's category names in the format: Sports:Football:Players I am trying to build a query that that locates all records that match the above category name OR if none exist its parent Sports:Football OR if none exist its parent Sports. The top level category, in this case Sports, will always have at least one matching record. I know I can do this with multiple queries by checking the previous query's result, but I am trying to build a query that does it all in one lookup to avoid lots of lookups in deep categories. Something along the logical lines of ... SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE category = 'Sports:Football' OR IF NONE category = 'Sports' ... of course the above query does not work but if anyone knows of how to accomplish something similar in one query I would much appreciate it. 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
Re: Excluding results based on contents of secondary table
Hi Moishe, The following query will solve your provlem. Select a.* from a left join b on a.c = b.c where b.c is null; Anvar. At 09:53 PM 01/11/2001 -0800, you wrote: Perhaps this is more of a general SQL question than a MySQL specific one, and I may be exposing my ignorance by asking this, but I figure it's worth a shot. I have two tables, 'a' and 'b'. Each contains a value 'c'. I want to select all 'a' rows where a.c is not in the set of b.c. Is this possible using a single query? What I do now is select all the b.c values, build up a where clause from those results, then select from a, but it's an extra query I'd rather not do. Thanks for any help anyone out there can provide. -Moishe - 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: Grouping behavior question
Hi Mr. Jerry, Since Mysql does not support sub query, you will have to use temporary table instead. Create temporary table tmptbl1 Select keycol, Max(datetimecol) as maxdate from detailtable group by Keycol; Create temporary table tmptbl2 Select distinct T.Keycol, D.Status from tmptbl1 T, detailtable D Where T.Keycol = D.keycol and T.maxdate = D.datetimecol; Select M.*, T2.Status From mastertable M, tmptbl2 T2 Where M.Keycol = T2.Keycol; I think this is the most straight forward way to solve your problem. I have not checked the above commands. Hope this works for you. Anvar. At 02:41 PM 02/11/2001 +1100, you wrote: I often find myself in the following situation: I have a master table and a detail table, the detail table contains chronologically ordered items (statuses) that relate to the master record. I now need to display one line for each of the master records with the latest status from the detail table. - 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: Will MySQL latest version support subqueries
Hi Madhuri, Subquery is in the immediate todo list of Mysql. It should be soon available. The alternative is to use temporary tables. You can read about temporary table in the Mysql manual. Section 3.5.4. would be helpful. Anvar. At 10:58 PM 30/10/2001 -0800, you wrote: hi, I have a clarification from u. Actually I am using MySQL 3.23.39 version. Does MySQL 4.0 or any latest version supports subQueries / subQueries with IN operator. How do I come over this problem. Plase do suggest if any alternatives available. Expecting great response from u. Awaiting quick and effective response, thanks and regards, Madhuri __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.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: Query help...
Hi Mr. David, I hope a bit of explanation for Steve Mayers' solution would help you. The query by Mayers select distinct a.username, a.ip from users a, users b where a.ip=b.ip a.username != b.username; I think I can explain the behaviour of the query: Suppose the table contains the data: username ip a 1 b 2 c 1 Considert the query without the where condition ie Select a.usernam, a.ip from users a, users b This is a self join cross product. A self join without a where condition would give the so called cross product. ie for each row in first table all the rows of the seccond table (3*3 = 9 rows). For clarity's sake we change the query to include the cols of the second table b ie issue the query Select a.*, b.* from users a, users b ab user ip user ip a 1 a 1 b 2 a 1 c 1 a 1 a 1 b 2 b 2 b 2 c 1 b 2 a 1 c 1 b 2 c 1 c 1 c 1 Now consider the first where condition ie a.ip = b.ip. This tells the database to include only those rows for which the ip col of table a is the same as that of b. The filtered output due to this clause is ab user ip user ip a 1 a 1 c 1 a 1 b 2 b 2 a 1 c 1 c 1 c 1 Add the second clause also to the query. ie the where condition becomes a.ip = b.ip and a.username != b.username : Now the rows from the above output where usernames of both a and b are the same are out. This gives the result. ab user ip user ip c 1 a 1 a 1 c 1 The distinct keyword is to eliminate duplicate rows from the result (Which may be there if in the user table contains the multiple rows with the same username and ip). I hope this clarifies. This all might seem a lot of complexity in the first sight. But once youstart thinking in SQL way these are quiet straight forward. With the promised subqueries in the new releases Mysql queries will surely get multiple fold complex and it would be a great and funny game. Anvar. Thanks!! Worked like a dream! I'm not quite sure why it knew to pull only ip's that are in there more than once though? David From: Steve Meyers [EMAIL PROTECTED] - I think I understand. This should work... select distinct a.username, a.ip from users a, users b where a.ip=b.ip a.username != b.username; Steve Meyers - 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 int value of char field? How to find...
Hi Simon, One way to solve the problem is this query: select max(field+0) from table; Since numeric 0 is added to field, mysql silently converts field column into numeric first and makes the calculation accordingly. Beware that pure character strings give zero when converted to numeric type so if all the pertinent data of your table are less than zero you will get 0 as the result. Anvar. . At 08:44 AM 22/10/2001 +0200, Kraa de Simon wrote: Hello all, How can I find the highest integer value in a result set like: 1 10 11 2 3 a ab abc I'm looking for the value 11. The statement 'select max(field) from table' gives me 'abc' so this won't do. Any ideas for a SQL statement that will do the trick? I'm using PHP / MySQL. Thanks! Simon. - 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
