 |
|
| Computers Forum Index » Computer - Databases - MS SQL Server » SQL Server 2000 - how to do MAX(a,b) or GREATEST(a,b)?... |
|
Page 1 of 1 |
|
| Author |
Message |
| Tony Mountifield... |
 Posted: Fri Oct 09, 2009 3:51 pm |
|
|
|
Guest
|
I have scoured the online TSQL manual at MSDN and haven't found the
answer to this question.
In SQL Server 2000, how can I do MAX(a,b), GREATEST(a,b), MIN(a,b) or
LEAST(a,b) without needing to evaluate both of the arguments twice?
CASE WHEN a < b THEN b ELSE a END is not suitable, because one of the
arguments is a sub-select.
What I am trying to do is something like this:
UPDATE foo SET x = MAX(0, x - (SELECT ......))
or this:
UPDATE foo SET x = x - MIN(x, (SELECT .....))
in order to prevent x going negative.
Any hints would be much appreciated!
Cheers
Tony
--
Tony Mountifield
Work: tony at (no spam) softins.co.uk - http://www.softins.co.uk
Play: tony at (no spam) mountifield.org - http://tony.mountifield.org |
|
|
| Back to top |
|
|
|
| Erland Sommarskog... |
| Posted: Fri Oct 09, 2009 9:05 pm |
|
|
|
Guest
|
Tony Mountifield (tony at (no spam) softins.clara.co.uk) writes:
Quote: I have scoured the online TSQL manual at MSDN and haven't found the
answer to this question.
In SQL Server 2000, how can I do MAX(a,b), GREATEST(a,b), MIN(a,b) or
LEAST(a,b) without needing to evaluate both of the arguments twice?
CASE WHEN a < b THEN b ELSE a END is not suitable, because one of the
arguments is a sub-select.
What I am trying to do is something like this:
UPDATE foo SET x = MAX(0, x - (SELECT ......))
or this:
UPDATE foo SET x = x - MIN(x, (SELECT .....))
in order to prevent x going negative.
Any hints would be much appreciated!
I think Plamen's hope for the queries being evaluated only once is
in vain - SQL Server does not subquery matching.
I assume that the subqueries are correlated? In such case, you should
look for a way to use the UPDATE FROM syntax instead. However, depending
how the subselects relates to the query, this may not be possible on
SQL 2000.
The last resort would be materialise all to a table. and then update
from the table.
--
Erland Sommarskog, SQL Server MVP, esquel at (no spam) sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |
|
|
| Back to top |
|
|
|
| Plamen Ratchev... |
| Posted: Fri Oct 09, 2009 9:38 pm |
|
|
|
Guest
|
Since those functions are not implemented in SQL Server you would have to use CASE expression and repeat the subqueries
in WHEN and THEN/ELSE. Hopefully the optimizer will evaluate each subquery only once.
--
Plamen Ratchev
http://www.SQLStudio.com |
|
|
| Back to top |
|
|
|
| Erland Sommarskog... |
| Posted: Sat Oct 10, 2009 8:43 am |
|
|
|
Guest
|
|
| Back to top |
|
|
|
| Tony Mountifield... |
| Posted: Sat Oct 10, 2009 10:07 pm |
|
|
|
Guest
|
In article <e-Sdner5BdKtY03XnZ2dnUVZ8tGdnZ2d at (no spam) giganews.com>,
David Portas <REMOVE_BEFORE_REPLYING_dportas at (no spam) acm.org> wrote:
Quote: "Tony Mountifield" <tony at (no spam) softins.clara.co.uk> wrote in message
news:hanm55$io8$1 at (no spam) softins.clara.co.uk...
I have scoured the online TSQL manual at MSDN and haven't found the
answer to this question.
In SQL Server 2000, how can I do MAX(a,b), GREATEST(a,b), MIN(a,b) or
LEAST(a,b) without needing to evaluate both of the arguments twice?
CASE WHEN a < b THEN b ELSE a END is not suitable, because one of the
arguments is a sub-select.
Try this:
UPDATE foo SET x =
(SELECT MAX(col)
FROM (SELECT .... /* your subquery */
UNION ALL SELECT 0) t);
Thanks to all for the responses, but I like this one best - very clever!
Cheers
Tony
--
Tony Mountifield
Work: tony at (no spam) softins.co.uk - http://www.softins.co.uk
Play: tony at (no spam) mountifield.org - http://tony.mountifield.org |
|
|
| Back to top |
|
|
|
| David Portas... |
| Posted: Sun Oct 11, 2009 1:35 am |
|
|
|
Guest
|
"Tony Mountifield" <tony at (no spam) softins.clara.co.uk> wrote in message
news:hanm55$io8$1 at (no spam) softins.clara.co.uk...
Quote: I have scoured the online TSQL manual at MSDN and haven't found the
answer to this question.
In SQL Server 2000, how can I do MAX(a,b), GREATEST(a,b), MIN(a,b) or
LEAST(a,b) without needing to evaluate both of the arguments twice?
CASE WHEN a < b THEN b ELSE a END is not suitable, because one of the
arguments is a sub-select.
Try this:
UPDATE foo SET x =
(SELECT MAX(col)
FROM (SELECT .... /* your subquery */
UNION ALL SELECT 0) t);
--
David Portas |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Thu Dec 10, 2009 3:12 am
|
|