select a.name, a.assignedTo, a.validFrom
from aliases a,
(select name, max(validfrom) as mvf
from aliases,
(select strftime('%s', 'now') as now) as c
where validFrom <= now
group by name) as b
where a.name=b.name and a.validfrom=b.mvf;
should also work. I changed the syntax slightly:
from a join b join c on a.x=b.y and b.z=c.p is just a different spelling of
from a, b, c where a.x=b.y and b.z=c.p
designed to make it clear (COBOL style) which clauses are join columns (ON) and
which are row selectors (WHERE). It should have zero effect on the actually
query plan.
Effectively, creates a temp table with a single row containing a single value
now, which is used as a selector against the rows of aliases. This format
should guarantee that the strftime function is only ever executed once.
sqlite> explain query plan select a.name, a.assignedTo, a.validFrom
...> from aliases a,
...> (select name, max(validfrom) as mvf
...> from aliases,
...> (select strftime('%s', 'now') as now) as c
...> where validFrom <= now
...> group by name) as b
...> where a.name=b.name and a.validfrom=b.mvf;
SELECT item[0] = {0:0}
item[1] = {0:1}
item[2] = {0:2}
FROM {0,*} = aliases (AS a)
{1,*} = SELECT agg_flag
item[0] = AGG{2:0}
item[1] = AGG_FUNCTION:max(AGG{2:2})
FROM {2,*} = aliases
{3,*} = SELECT FUNCTION:strftime(item[0] = '%s'
item[1] = 'now')
END (tabname=sqlite_subquery_53E850_) (AS c)
WHERE LE({2:2},{3:0})
GROUPBY {2:0}
END (tabname=sqlite_subquery_53E4D0_) (AS b)
WHERE AND(EQ({0:0},{1:0}),EQ({0:2},{1:1}))
END
sele order from deta
---- ------------- ---- ----
1 0 1 SCAN SUBQUERY 2 AS c (~1 rows)
1 1 0 SCAN TABLE aliases (~333333 rows)
1 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 1 SCAN SUBQUERY 1 AS b (~100 rows)
0 1 0 SEARCH TABLE aliases AS a USING AUTOMATIC COVERING
INDEX (name=? AND validFrom=?) (~7 rows)
sqlite>
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
