how to get Previous date

[Chenri J]

I have a price table entered more than once every day, 
In the report for spesific day, I must show also the last entered price for the 
day before
The problem is that the day before sometimes varies not fixed 
(if it is fixed I could just round down the required date substract the date 
with 86400 to get
previous day)

How can I get the previous date 

ID DATE ProductName PRICE Last
01 1/1/04 ProductABCD 1500 NO
02 1/1/04 ProductABCD 1600 YES
03 1/2/04 ProductABCD 1000 NO
04 1/2/04 ProductABCD 1100 YES
05 1/4/04 ProductABCD 1900 NO
06 1/4/04 ProductABCD 1800 NO

Report required for ‘1/4/04’

Previous Price : 1100 (ID: 04)
05 1/4/04 ProductABCD 1900 NO
06 1/4/04 ProductABCD 1800 NO

How do I Get 1100?




__ 
Do you Yahoo!? 
Send a seasonal email greeting and help others. Do good. 
http://celebrity.mail.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

if you know the answer,please answer immediately contd

[sirisha gnvg]

 
 
I am a student and we are doing project in mysql database monitoring.Yesterday 
I posted my doubts regarding that project to this forum.
 
I got reply from Mr.Harish, his suggestion is to use show table status 
 or myisamchk -d -v  .Thank you sir for your reply.
 
Actually i haven't made the queations clearer.
 
This is what we have to do in our project,we have to monitor mysql database and 
continously calculate memory already used by and free memory available for each 
database(or table),each log file,each temporary table(mysql temporary 
tables).If free memory is less than a certain percentage we have to send an 
alert box to the user asking him to backup those tables and logfiles.
So we need information about both used space and free space not just the size 
of those tables or log files.
 
I asked to give details about the tables myisamchk  works on.By that question I 
mean that because Myisamchk is giving details about memory used by tables those 
details should have been stored in some system tables or files.We need the 
names of those system tables or files.
   
   So if you have information regarding any of the above issues please 
answer immediately.
 
yours sincerely,
sirisha.  


Yahoo! India Matrimony: Find your life partneronline.

Re: Calculating User Ranks (SQL Query Question)

[Don Read]

On 22-Dec-2004 Michael J. Pawlowsky wrote:
> I’m trying to come up with a more efficient method to do this.
> I have a table where people enter some info into the table.
> 

 

> I would like to allow the users to be able to see where they stand
> rank 
> wise with everyone else.
> Right now I basically do a SELECT count(1) as entries, user_id GROUP
> BY 
> user_id ORDER BY entries DESC.

 

> I was wondering if anyone could think of a better way to do this.
> 

Add a rank column that gets recalculated after each entry is added?

php code:

function calc_user_ranks($limit=100) {
global $dbconn;

$dbconn->Execute('SET @x:=0');
$qry = "SELECT (@x:[EMAIL PROTECTED]) AS rank, user_id, count(1) AS 
entries 
  FROM user 
  GROUP BY user_id ORDER BY entries DESC LIMIT $limit";
$dbconn->Execute($qry);
while($row = $r->FetchRow()) 
$rank[$row['user_id']] = $row['rank'];
$dbconn->Execute('UPDATE user SET rank=0');
foreach($rank as $id => $r) {
$qry = "UPDATE user SET rank=$r WHERE user_id=$id";
$dbconn->Execute($qry);
}
}

Regards,
-- 
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: if you know please answer immediately

[Harish]

And, by the way the myisamchk command will be myisamchk -dvv 

Eg: $ cd /home/mysql-data/data/test
Considering the mysql data path is set in /home/mysql-data/

Here "test" is the database name.

$ll
-rw-rw1 mysqlmysql8660 May 17  2004 abc.frm
-rw-rw1 mysqlmysql 570 May 17  2004 abc.MYD
-rw-rw1 mysqlmysql1024 Dec 25 10:42 abc.MYI

"abc" is the table name.

So, the command which can be used is myisamchk -dvv
/home/mysql-data/data/test/abc


- Harish

-Original Message-
From: sirisha gnvg [mailto:[EMAIL PROTECTED]
Sent: Saturday, December 25, 2004 12:30 AM
To: mysql@lists.mysql.com
Subject: if you know please answer immediately


>I am a student and we are doing a project in mysql database monitoring.

> 1 . we found that the memory used and free memory available for tables in
individual database is given by myisamchk -d -v command.But we don't have
any information
about the tables or files myisamchk works on.so please give information if
available.

> 2 . we are required to find memory used and free memory available for all
types of tables,log files of mysql,temporary tables used by mysql
database.We don't get help from any of our professors since they don't have
working experience in mysql.

>please give details to any one of the above questions if not all the
questions.



Yahoo! India Matrimony: Find your life partneronline.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Data conversion question

[Yves Arsenault]

Thanks Konrad,

Yves


On Thu, 23 Dec 2004 23:28:31 +0100, Konrad Kieling <[EMAIL PROTECTED]> wrote:
> > Does MySQL have commands that would allow me to convert Base64 data to
> > Binary and then convert that Binary to a string format?
> have a look at the attached file (hope the attachment did not get
> stripped). it contains some udf-functions for base64 en/de-coding. a
> little description is included.
> 
> ciao,
> konrad
> 
> 
> 


-- 
Yves Arsenault

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: if you know please answer immediately

[Harish]

Dear Sirisha,

The command "show table status from " will give you the
details pertaining to the type of the table(MyISAM/InnoDB) and also the
memory used.

Hope this helps you.

-Harish

-Original Message-
From: sirisha gnvg [mailto:[EMAIL PROTECTED]
Sent: Saturday, December 25, 2004 12:30 AM
To: mysql@lists.mysql.com
Subject: if you know please answer immediately


>I am a student and we are doing a project in mysql database monitoring.

> 1 . we found that the memory used and free memory available for tables in
individual database is given by myisamchk -d -v command.But we don't have
any information
about the tables or files myisamchk works on.so please give information if
available.

> 2 . we are required to find memory used and free memory available for all
types of tables,log files of mysql,temporary tables used by mysql
database.We don't get help from any of our professors since they don't have
working experience in mysql.

>please give details to any one of the above questions if not all the
questions.



Yahoo! India Matrimony: Find your life partneronline.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

if you know please answer immediately

[sirisha gnvg]

>I am a student and we are doing a project in mysql database monitoring. 

> 1 . we found that the memory used and free memory available for tables in 
> individual database is given by myisamchk -d -v command.But we don't have any 
> information 
about the tables or files myisamchk works on.so please give information if 
available. 

> 2 . we are required to find memory used and free memory available for all 
> types of tables,log files of mysql,temporary tables used by mysql database.We 
> don't get help from any of our professors since they don't have working 
> experience in mysql. 

>please give details to any one of the above questions if not all the questions.



Yahoo! India Matrimony: Find your life partneronline.

Re: Out of memory

[lists]

Philippe Poelvoorde wrote:
Hi,
mysqldump returns with this error on one of my nightly batch :
mysqldump: Out of memory (Needed 8164 bytes)
mysqldump: Got error: 2008: MySQL client run out of memory when 
retrieving data from server
Fri Dec 24 00:00:22 CET 2004

Is there any way to avoid this particular error ?
(bear I mind that I don't have any access to my.cnf on this environnement!)
The last table in the dump (and not dump, it stops just after the CREATE 
TABLE) is not that big (less than 8Mo on disk).

See what happens if you try it with the --quick option.  This will 
prevent mysqldump from writing to memory before writing to the dump file.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Sum on Float type

[Hassan Schroeder]

sam wun wrote:
I created a Transaction table with a field Cost which is a Float type 
with only 2 precision eg. 123.01.
When I use the following sql statement to make a sum of this field, it 
returned a Float number with more than 2 precision numbers 

If I should not use Float type for the Cost field, what type should I use?
For currency fields, I use DECIMAL. See:
  
HTH!
--
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design ===  (+1) 408-938-0567   === http://webtuitive.com
  dream.  code.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Sum on Float type

[Mechain Marc]

You can use the format() function:

select t.custcode, format(sum(t.cost),2) as "Sub-Total"
from customer c, transaction t
where c.custcode = t.custcode
group by t.custcode

Marc.

-Message d'origine-
De : sam wun [mailto:[EMAIL PROTECTED]
Envoyé : vendredi 24 décembre 2004 11:23
À : mysql@lists.mysql.com
Objet : Sum on Float type 


Hi,

I created a Transaction table with a field Cost which is a Float type 
with only 2 precision eg. 123.01.
When I use the following sql statement to make a sum of this field, it 
returned a Float number with more than 2 precision numbers  eg. 456.92384933

select t.custcode, sum(t.cost) as "Sub-Total"
from customer c, transaction t
where c.custcode = t.custcode
group by t.custcode

I don't know why the Sum function returns more than 2 precision number.
If I should not use Float type for the Cost field, what type should I use?

Thanks
Sam


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Optimising a query on a large table.

[Rob Keeling]

Thanks to all that replied,

it turns out that the great delay was due to the use of the "like"
opperator,
I did have an index on person_id + session_start, just a thought though,
using this will mysql pull out data ordered as per the index?

Yes, sql is VERY limited, thanks for the pointers.

Merry Christmas.

Rob Keeling

"Donny Simonton" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Rob,
> First of all I would say, your query is pretty badly laid out.  First,
> unless you need every fields from a table returned only ask for those
> specific fields, and do you have an index on the combination of person_id
+
> session_start?  If not, your query will always be slow.
>
> But this is how I would write it.
>
> Select bla, bla2 from table
> where person_id = 10
> and session_start between ('2004-09-01 00:00:00' and '2004-10-01
00:00:00')
>
> Then why would you order by session_start, when the odds are that you
added
> the data to the table by time anyway.  So why waste the servers time
> ordering something that may already be ordered for you automatically.  But
> you would know that better than any of us.
>
> A query like this should take no longer than 0.1 seconds to execute in
most
> cases, even with a few gigs of data.
>
> Doonny
>
> > -Original Message-
> > From: news [mailto:[EMAIL PROTECTED] On Behalf Of Rob Keeling
> > Sent: Thursday, December 23, 2004 11:55 AM
> > To: mysql@lists.mysql.com
> > Subject: Optimising a query on a large table.
> >
> > I have a  152MB MyISAM  table that I am trying to execute a simple
select
> > statement on,
> > I need to retreave all rows with a given index, sorted by date.
> >
> > This is taking a very long period of time to execute.
> >
> > What can I do to speed up the query.
> >
> > The sql is,
> >
> > SELECT * FROM table WHERE (person_id LIKE 10) AND (session_start >
> > '2004-09-01 00:00:00') AND (session_start < '2004-10-01 00:00:00') Order
> > by
> > session_start
> >
> > Thanks
> >
> > Rob Keeling
> >
> >
> > -
> > --
> >
> > I love deadlines.   I love the whooshing noise they make as they go by.
> > - Douglas Adams
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Out of memory

[Philippe Poelvoorde]

Hi,
mysqldump returns with this error on one of my nightly batch :
mysqldump: Out of memory (Needed 8164 bytes)
mysqldump: Got error: 2008: MySQL client run out of memory when 
retrieving data from server
Fri Dec 24 00:00:22 CET 2004

Is there any way to avoid this particular error ?
(bear I mind that I don't have any access to my.cnf on this environnement!)
The last table in the dump (and not dump, it stops just after the CREATE 
TABLE) is not that big (less than 8Mo on disk).

--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Help, Two Tables...

[Roger Baklund]

Jason Caldwell wrote:
Hi
 
I have two tables; tbl_Headers and tbl_SubItems.

tbl_Headers contain my Header Items such as (fields: ID & HEADER)
ID HEADER
---
1.00   TOPIC ONE
2.00   TOPIC TWO
3.00   TOPIC THREE
tbl_SubItems contain Sub Header Items such as (fields: ID & SUBITEM)
ID SUBITEM
---
1.01   SubItem 1
1.02   SubItem 2
1.03   SubItem 3
2.01   SubItem 1
2.02   SubItem 2
3.01   SubItem 1
3.02   SubItem 2
etc and so on ...
I want to output a list like so:
1.00 TOPIC ONE
1.01 SubItem 1
1.02 SubItem 2
1.03 SubItem 3
2.00 TOPIC TWO
2.01 SubItem 1
2.02 SubItem 2
3.00 TOPIC THREE
3.01 SubItem 1
3.02 SubItem 2
etc and so on ...
I'm brain-cramping on this, can someone please help ?  
If you use version 4.x of mysql you could use a UNION:
(SELECT * FROM tbl_Headers)
  UNION
(SELECT * FROM tbl_SubItems)
  ORDER BY ID;
http://dev.mysql.com/doc/mysql/en/UNION.html >
If you use mysql version 3.x: It would be easy if all items was in the 
same table, then you could just order by ID... A temporary table can 
help, try something like this:

CREATE TEMPORARY TABLE tmp1
  SELECT ID,HEADER as ITEM FROM tbl_Headers;
INSERT INTO tmp1 SELECT ID, SUBITEM as ITEM FROM tbl_SubItems;
SELECT * FROM tmp1 ORDER BY ID;
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Sum on Float type

[sam wun]

Hi,
I created a Transaction table with a field Cost which is a Float type 
with only 2 precision eg. 123.01.
When I use the following sql statement to make a sum of this field, it 
returned a Float number with more than 2 precision numbers  eg. 456.92384933

select t.custcode, sum(t.cost) as "Sub-Total"
from customer c, transaction t
where c.custcode = t.custcode
group by t.custcode
I don't know why the Sum function returns more than 2 precision number.
If I should not use Float type for the Cost field, what type should I use?
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Mysql-4.1.8 library name bug

[Andrey Kotrekhov]

Добрый день.
Yes, this has been reported on this list a couple times already.
Really baffling how this one made it out of QA.
Thank you for your answer.
Are there any plans to release snapshort bugfix?
Josh
On Thu, 23 Dec 2004 21:43:27 +0200 (EET)
Andrey Kotrekhov <[EMAIL PROTECTED]> wrote:
SQL
Hello, All!
IMHO this the bug in 4.1.8 to create library shared libraries
without .so suffix.
After this any programs linked with static libraries not dynamic,
because of convention lib*.so.[0-9] in shared libraries names.
ldconfig doesn't see  new libraries at all too.
  This bug in 4.1.8
4.1.7 compiled on the same PC at the same time with .so suffix in
library names.
 Best regards.
___
Andrey Kotrekhov [EMAIL PROTECTED]
ISP Alkar Teleport
???. +380 562 34-00-44
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Best regards.
___
Andrey Kotrekhov [EMAIL PROTECTED]
ISP Alkar Teleport
тел. +380 562 34-00-44
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]