Perhaps this is something like what your looking for? select substring(round(number,digits) ,1,instr(round(number,digits),'.')+digits) as roundednumber,digits,number from testme
Regards, Dan > -----Original Message----- > From: DL Neil [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, 27 February 2002 6:32 a.m. > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: Can somebody help me with "round (columna,columnb) "? > > > Hi Richard, > > > i want to round() the value of a column, but the number of digits is > > depending on the value of another column. i found out, that > this doesn't work: > > > > create table testme > > -> (number double (5,4),digits tinyint(3)); > > Query OK, 0 rows affected (0.08 sec) > > > > mysql> explain testme; > > +--------+-------------+------+-----+---------+-------+ > > | Field | Type | Null | Key | Default | Extra | > > +--------+-------------+------+-----+---------+-------+ > > | number | double(6,4) | YES | | NULL | | > > | digits | tinyint(3) | YES | | NULL | | > > +--------+-------------+------+-----+---------+-------+ > > 2 rows in set (0.08 sec) > > > > mysql> insert into testme values (100.4235,3),(85.4,1); > > Query OK, 2 rows affected (0.08 sec) > > Records: 2 Duplicates: 0 Warnings: 0 > > > > mysql> select * from testme; > > +----------+--------+ > > | number | digits | > > +----------+--------+ > > | 100.4235 | 3 | > > | 85.4000 | 1 | > > +----------+--------+ > > 2 rows in set (0.08 sec) > > > > mysql> select round(number,digits) from testme; > > +----------------------+ > > | round(number,digits) | > > +----------------------+ > > | 100.4240 | > > | 85.4000 | > > +----------------------+ > > 2 rows in set (0.08 sec) > > > > > > is there any way i could do this with *one* query? > > the only solution i see is to read the digits-column with a > script and then > > create a new query, but thats not possible in my case... > > > It does the same for me (that's not much help, but it's obviously > not 'you'!) > > I first went looking to see if there is a MySQL parameter which > says how many decimal digits will be displayed, > by default - but failed to find it and may be confusing myself > with another product. Certainly nothing in > my.cnf. > > I then wondered if the reason the result is expressed this way is > because of the schema which defines: > > number double (5,4) > > Sure enough, changing number to double (5,3) and then re-running > your tests, makes it change. Again no help to > you. > > I notice that all of the examples in the manual for round(), > truncate(), and format() render the D argument as a > constant. So I'm wondering if the "digits" is being ignored? > > Sorry not to have more to offer. Is there a wiser mind that can > shed some light? > =dn > > > > --------------------------------------------------------------------- > 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
