SQLite CVSTrac

*** 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 ----

sqlite - Unsupported Sql