Difference between revisions of "ABS"
From SQLZOO
| (10 intermediate revisions by one user not shown) | |||
| Line 1: | Line 1: | ||
| − | < | + | <table align='right' border='1'> |
| − | + | ||
| − | + | ||
<caption>Compatibility</caption> | <caption>Compatibility</caption> | ||
| − | < | + | <tr><th colspan='3'>ABS(f)</th></tr> |
| − | <tr>< | + | <tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr> |
<tr><td align='left'>ingres</td><td>Yes</td><td></td></tr> | <tr><td align='left'>ingres</td><td>Yes</td><td></td></tr> | ||
<tr><td align='left'>mysql</td><td>Yes</td><td></td></tr> | <tr><td align='left'>mysql</td><td>Yes</td><td></td></tr> | ||
| Line 10: | Line 8: | ||
<tr><td align='left'>postgres</td><td>Yes</td><td></td></tr> | <tr><td align='left'>postgres</td><td>Yes</td><td></td></tr> | ||
<tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr> | <tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr> | ||
| − | </table></ | + | </table> |
| + | <h1>ABS</h1> | ||
<p>ABS returns the absolute value. The output is positive even if the input is negative:</p> | <p>ABS returns the absolute value. The output is positive even if the input is negative:</p> | ||
| − | <pre>ABS(x) = x if x>=0 | + | <pre style='width:40ex'>ABS(x) = x if x>=0 |
| − | ABS(x) = -x if x<0</pre> | + | ABS(x) = -x if x<0</pre> |
<div class='qu'> | <div class='qu'> | ||
| Line 19: | Line 18: | ||
The value 70000 is the target value, 500 is the "tolerance" so the test ABS(area-70000)<500 tests that the area is between 70000-500 and 70000+500. That is 69500<area<70500 | The value 70000 is the target value, 500 is the "tolerance" so the test ABS(area-70000)<500 tests that the area is between 70000-500 and 70000+500. That is 69500<area<70500 | ||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
SELECT name, area FROM bbc | SELECT name, area FROM bbc | ||
WHERE ABS(area-70000)<500 | WHERE ABS(area-70000)<500 | ||
| Line 29: | Line 23: | ||
</div> | </div> | ||
| + | <p>See also</p> | ||
<ul> | <ul> | ||
| − | <li>[[CASE statement]]</li> | + | <li>[[CASE |CASE statement]]</li> |
</ul> | </ul> | ||
| + | |||
| + | {{Languages}} | ||
Latest revision as of 12:35, 6 September 2012
| ABS(f) | ||
|---|---|---|
| Engine | OK | Alternative |
| ingres | Yes | |
| mysql | Yes | |
| oracle | Yes | |
| postgres | Yes | |
| sqlserver | Yes | |
ABS
ABS returns the absolute value. The output is positive even if the input is negative:
ABS(x) = x if x>=0 ABS(x) = -x if x<0
ABS can be useful for testing values that are "close". For example this query shows each country that has area that is roughly 70 thousand. The value 70000 is the target value, 500 is the "tolerance" so the test ABS(area-70000)<500 tests that the area is between 70000-500 and 70000+500. That is 69500<area<70500
SELECT name, area FROM bbc WHERE ABS(area-70000)<500
See also
| Language: | English • Deutsch |
|---|
