You guys have been so helpful with this, I'm hoping that I can ask for
one more favor...
The reason I needed the greatest(max()) functionality, was to run the
following query...I can make it work from the command line, but
everytime I run it from PHP, the MySQL service shuts down, and needs
to be restarted manually.
I'm calling a stored procedure 'selectAllRequests' which is the following query:
SELECT
r.id,
r.partNumber,
r.OtherFields,
functionGetHighestValue(r.partNumber, r.qty) AS 'highestValue'
FROM request r
WHERE r.deleted=0
ORDER BY highestValue DESC, r.dateSent DESC;
the function I'm calling is as follows:
CREATE FUNCTION `functionGetHighestValue`(`MPNParam` varchar(60),
`qtyParam` DOUBLE(10,4)) RETURNS DOUBLE(10,4)
BEGIN
DECLARE dHighest DOUBLE(10,4) DEFAULT 0;
SELECT
GREATEST(MAX(i.distySellCost), MAX(i.originalCost),
MAX(i.unitCost),
MAX(i.unitSellCost))*qtyParam
FROM inventory i
WHERE i.MPN = 'MPNParam' AND i.status=1 INTO dHighest;
RETURN dHighest;
END|
As I say, I can call this procedure from the command line and it
works, but calling it from PHP results in the MySQL service crashing
on my Windows 2003 server. I'm using PHP 5.0.4 and MySQL 5.0.4. Any
help is appreciated. Thanks.
On 5/27/05, Scott Klarenbach <[EMAIL PROTECTED]> wrote:
> select greatest(max(col1), max(col2), max(col3), max(col4)) from table
> works the best, as Keith pointed toward initially. Remember, I forgot
> to mention that I wanted the greatest for the whole table, not just
> for each row....so, 10, 12, 8 is not what I wanted...out of
>
> 10 2 3
> 5 4 8
> 1 12 7
>
> i want 12.
>
> thanks again.
>
> On 5/27/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > I forgot :
> >
> > 10, 12, 8 is not a row !!!
> >
> > Mathias
> >
> > Selon [EMAIL PROTECTED]:
> >
> > > Hi Keith,
> > > yes concat makes an associative lost for max.
> > > But if we split the desc on all the columns, it works :
> > >
> > > mysql> select * from numbers
> > > -> order by a desc,b desc,c desc
> > > -> limit 1;
> > > +------+------+------+
> > > | a | b | c |
> > > +------+------+------+
> > > | 10 | 2 | 3 |
> > > +------+------+------+
> > > 1 row in set (0.00 sec)
> > >
> > > it's a real desc ordering.
> > >
> > > Thanks
> > >
> > > Mathias
> > >
> > >
> > > Selon Keith Ivey <[EMAIL PROTECTED]>:
> > >
> > > > [EMAIL PROTECTED] wrote:
> > > > > Hi all,
> > > > > what is max ? it's the first row when we sort data in descending
> > > > > order.
> > > > >
> > > > > so
> > > > >
> > > > > select col1,col2,col3,col4 ... from table
> > > > > order by concat(col1,col2,col3,col4 ... ) desc
> > > > > LIMIt 1;
> > > > >
> > > > > should be silar to what is needed. I say should :o)
> > > >
> > > > That would only work if the greatest values for col2, col3, col4, etc.,
> > > > all
> > > > occurred in the same row with the greatest value for col1, and if all
> > > > the
> > > > values
> > > > for col1 had the same number of digits (and the same for col2, col3,
> > > > etc.).
> > > >
> > > > Consider this table:
> > > >
> > > > 10 2 3
> > > > 5 4 8
> > > > 1 12 7
> > > >
> > > > Your query would give 5, 4, 8 (because "548" as a string is greater than
> > > > "1023"
> > > > or "1127"), but he wants 10, 12, 8.
> > > >
> > > > --
> > > > Keith Ivey <[EMAIL PROTECTED]>
> > > > Smokefree DC
> > > > http://www.smokefreedc.org
> > > > Washington, DC
> > > >
> > >
> > >
> > >
> >
> >
> >
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
