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
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
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
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
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
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;
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
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
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
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
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)
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
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
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:
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
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
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
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
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
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
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 :
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
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
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
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 :
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
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
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
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
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
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
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(
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
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
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
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
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
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
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
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(
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
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
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;
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
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
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
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
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,
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,
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
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:
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
52 matches
Mail list logo
