On Sep 11, 2012, at 9:13 PM, Wiktor Adamski <[email protected]> wrote:
>>> 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 analytics, the only thing one really need to do is
>> to mark the lead row for selection. No point to over complicate things as
>> above).
>
> what do you mean by "to mark the lead row for selection" ? is there a
> database that has something simpler to use than qualify + row_number() ? (yes
> i know, min can be replaced by first_value() or order by in most aggregates
> can/should be removed, but after those changes it's still the same query)
I suspect QUALIFY is SQL Server specific, no?
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
from foo
)
select *
from DataSet
where DataSet.is_lag = 1
That's all. Only one analytic needed.
LAG and LEAD Analytic Functions
http://www.oracle-base.com/articles/misc/lag-lead-analytic-functions.php
Ditto for Postgres:
http://www.postgresql.org/docs/devel/static/functions-window.htm
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
