On 2014-08-26 18:00, sqlite-users-requ...@sqlite.org wrote:
SELECT
employee_name,
employee_id,
salary,
rank() OVER(PARTITION BY dept ORDER BY salary DESC),
100.0*salary/sum(salary) OVER (PARTITION BY dept)
FROM employee;
I don't know if the above is valid SQL or not. But is
With regular ISO window functions, one could typically write something along
these lines:
with
DataSet
as
(
select foo.*,
lag( 0, 1, 1 ) over ( partition by id order by time ) as is_lag
fromfoo
)
select *
fromDataSet
where DataSet.is_lag = 1
you're right. it
select id, a, min(mtime) over(partition by id order by mtime) m from tab
qualify row_number() over(partition by id order by mtime) = 1
While using analytics would indeed be the best approach overall, these are
sadly not supported in SQLite in any ways or forms.
(For the record, if using
> I believe OVER() is an Oracle-specific extension to SQL, not a
> standard in any way.
ISO/IEC 9075-2:2003:
::= OVER
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> Apparently, using such a function in ORDER BY
> clause alone doesn't make the statement aggregate (whether it should is
> perhaps debatable)
I suppose this may be in the standart. I'm 100% sure that this one is
allowed by standart:
... ORDER BY avg(a) OVER()
so likely ORDER BY avg(a) is also
> In the first query, there is an aggregate in the result set, so an
> implicit GROUP BY is used. The ORDER BY is meaningless, but not an
> error (and could be more easily written "ORDER BY 1"; see below).
The order is not meaningless. It can return an error or do nothing. If
aggregate in
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a);
sqlite> select avg(a) from t order by avg(a); -- order by aggregate
possible
sqlite> select 1 from t order by a; -- order by column not in result
possible
> Changing the 2 "15g" entries in sqlite3.c to "16g" corrects this problem. 15
> digits is all that is guaranteed but the vast majority of 16-digit values are
> representable.
>
> Is this a valid solution? Or are there other side effects?
It should be ok. However there's another bug that will
SQLite version 3.7.8 2011-09-19 14:49:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a float);
sqlite> insert into t values(1125899906842624);
sqlite> select a = cast(cast(a as text) as float) from t;
0
Yes, I know - 16 digits. But
Look at disc transfer. With 16 queries in one thread on single disc,
disc may be accesed linearly (depeding on query). With 16 threads
accesing disc at the same time linear disc access is impossible
(however os may do some prefetching) and queries will by slower. You
may try increasing page size -
http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> I was trying to create an index on an integer column in a very large
> table with over 400,000,000 rows on an Ubuntu ... I
> increased the cache size to 2 but to no avail.
That's only 200M of cache and your table is much larger. Sqlite can't
index/sort efficiently lare data on disc -
> In short, you cannot assume conditions are processed left-to-right,
> including JOIN conditions.
I admit that i haven't checked the snadart but I did check other
engines and they evaluate from left to right. I think that long time
ago I'v read in a standart that 3 table join is basicly
> If you join
> tables which have the same column names, you need to use the table names.
You are right, but joined tables don't have the same column names.
SELECT * FROM t1 JOIN t2 ON a = b -- there is only one 'a' and that
select is correctly implemented
SELECT * FROM t1 JOIN t2 ON a = b JOIN
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t1 (a INT);
sqlite> CREATE TABLE t2 (b INT);
sqlite> CREATE TABLE t3 (a INT);
sqlite> SELECT * FROM t1 JOIN t2 ON t1.a = t2.b AND a /* obviously
t1.a */ != 1 JOIN t3 ON t1.a = t3.a;
There is a lot more synchronization in 3.7.4. If you disable it new
version may be faster.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a);
sqlite> select group_concat(distinct a) from t;
sqlite> select group_concat(distinct a, ',') from t;
Error: DISTINCT aggregates must have exactly one argument
Both queries
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a int);
sqlite> insert into t values(1);
sqlite> select 0 where 0;
sqlite> update t set a = (select 0 where 0);
sqlite> select * from t;
sqlite> select 0 limit 0;
sqlite>
> There were many problems with
> that approach:
...
> (3) Each table and index is in a
> separate file so your "database" was a directory full of files instead
> of a single file
This one is not a problem. Actually I don't see how 1 file is better
than 1 directory. For example mac
> Already fixed. Seehttp://www.sqlite.org/src/info/f74beaabde
I'm might be wrong because I didn't check with the newest binary, but
if I remember correctly this fix will compare t3.a to t1.a (instead of
t2.a). So it will produce different results (first select will be 1|2
and second will be
SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t1(a int);
sqlite> create table t2(a int);
sqlite> create table t3(a int);
sqlite> insert into t1 values(1);
sqlite> insert into t2 values(2);
sqlite> insert into t3 values(1);
There's no reason for following error:
SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .headers ON
sqlite> create table t1(a int);
sqlite> create table t2(a int);
sqlite> create table t3(a int);
sqlite> insert into t1 values(1);
sqlite>
SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t1(a int);
sqlite> create table t2(a int);
sqlite> create table t3(a int, b int);
sqlite> insert into t1 values(1);
sqlite> insert into t3 values(1, 1);
sqlite> select * from t1
> Richard, Before you "fix" it, I'm not convinced it is broken.
>
> >From MS SQL server
>
> create table _names (N varchar(5));
> insert into _names values('a');
> insert into _names values('b');
> insert into _names values('c');
> insert into _names values('d');
> insert into _names values('e');
24 matches
Mail list logo
