Difference between revisions of "Maximum"
| (One intermediate revision by one user not shown) | |||
| Line 1: | Line 1: | ||
| − | + | In this example you are shows how to use the MAX or MIN functions to find the maximum or minimum over two fields instead of just one. | |
<div class='ht'> | <div class='ht'> | ||
<div class=params>schema:scott</div> | <div class=params>schema:scott</div> | ||
| Line 17: | Line 17: | ||
min(x,y) = (x + y - ABS(x-y))/2 | min(x,y) = (x + y - ABS(x-y))/2 | ||
</div> | </div> | ||
| − | <source lang='sql' class='def'>SELECT id, x, y, (x+y+ABS(x-y))/2 | + | <source lang='sql' class='def'> |
| − | FROM t</source> | + | SELECT id, x, y, (x+y+ABS(x-y))/2 |
| + | FROM t</source> | ||
<div class="ecomm e-mysql" style="display: none">In MySQL you can also use the <code>GREATEST</code> or <code>LEAST</code> functions to do this.</div> | <div class="ecomm e-mysql" style="display: none">In MySQL you can also use the <code>GREATEST</code> or <code>LEAST</code> functions to do this.</div> | ||
<div class="ecomm e-oracle" style="display: none">In Oracle you can also use the <code>GREATEST</code> or <code>LEAST</code> functions to do this.</div> | <div class="ecomm e-oracle" style="display: none">In Oracle you can also use the <code>GREATEST</code> or <code>LEAST</code> functions to do this.</div> | ||
Latest revision as of 15:04, 2 August 2012
In this example you are shows how to use the MAX or MIN functions to find the maximum or minimum over two fields instead of just one.
DROP TABLE t
CREATE TABLE t( id VARCHAR(10), x INTEGER, y INTEGER ); INSERT INTO t VALUES ('A',1,2); INSERT INTO t VALUES ('B',4,3); INSERT INTO t VALUES ('C',5,5);
The function for using MAX over two fields is
max(x,y) = (x + y + ABS(x-y))/2
The function for using MIN over two fields is
min(x,y) = (x + y - ABS(x-y))/2
SELECT id, x, y, (x+y+ABS(x-y))/2 FROM t
Hack 10 Converting subqueries into joins
Hack 11 Converting aggregate subqueries into joins
Hack 16 Search for a String across columns
Hack 24 Multiply Across a Result Set
Hack 25.5 Splitting and combining columns
Hack 26 Include the rows your JOIN forgot
Hack 30 Calculate the maximum/minimum of two fields
Hack 33 Get values and subtotals in one shot
Hack 50 Combine tables containing different data
Hack 51/52 Display rows as columns
Hack 55 Import Someone Else's Data
Hack 62 Issue Queries Without Using a Table
Hack 63 Generate rows without tables
Hack 72 Extract a subset of the results
Hack 78 Break it down by Range
Hack 88 Test two values from a subquery
