*** 39,44 **** --- 39,61 ---- START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE) + *: 2004.01.08 : MEDIAN and standard deviation... are they standard? Essential for sqlite standalone executable for shell script users. + + _:_MEDIAN is difficult because it cannot be done "on-line," i.e., on a stream + of data. Following is a + solution to MEDIAN credited to David Rozenshtein, Anatoly Abramovich, and + Eugene Birger; it is + explained here: http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html + + SELECT x.Hours median + FROM BulbLife x, BulbLife y + GROUP BY x.Hours + HAVING + SUM(CASE WHEN y.Hours <= x.Hours + THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND + SUM(CASE WHEN y.Hours >= x.Hours + THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1 + *: 2004.01.01 : DISTINCT ON (expr,...) - this is from Postgres, where expr,... must be the leftmost expressions from the ORDER BY clause *: 2003.10.30 : INSERTing fewer values than columns does not fill the missing columns with the default values; if fewer values than columns in the table are supplied, all columns filled have to be named before the keyword values *************** *** 76,100 **** using a one way hash. See the MySQL function MD5 or Password for examples. *:FLOOR and CEILING functions, e.g. "SELECT FLOOR(salary) FROM personnel;" - - *: MEDIAN and standard deviation... are they standard? Essential for sqlite - standalone executable for - shell script users. - - _:_MEDIAN is difficult because it cannot be done "on-line," i.e., on a stream - of data. Following is a - solution to MEDIAN credited to David Rozenshtein, Anatoly Abramovich, and - Eugene Birger; it is - explained here: http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html - - SELECT x.Hours median - FROM BulbLife x, BulbLife y - GROUP BY x.Hours - HAVING - SUM(CASE WHEN y.Hours <= x.Hours - THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND - SUM(CASE WHEN y.Hours >= x.Hours - THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1 *: Oracle's join syntax using (+) and (-): --- 93,98 ----