Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Sep 11, 2012, at 9:53 PM, Petite Abeille wrote: > I suspect QUALIFY is SQL Server specific, no? Teradata perhaps… drifting even farther away from ISO/ANSI :P ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Sep 11, 2012, at 9:13 PM, Wiktor Adamski 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,

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Sep 11, 2012, at 2:48 PM, bardzotajneko...@interia.pl 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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

To: General Discussion of SQLite Database > Subject: [sqlite] Finding rows with MIN(MTIME) for all IDs > Message-ID: > > Content-Type: text/plain; charset=ISO-8859-1 > > Suppose you have a table like this: > > CREATE TABLE tab(ID, A, B, C, ..., MTIME); > > T

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Sep 5, 2012, at 4:57 PM, Richard Hipp wrote: > I think I'll stick with SQLite's magical processing of min()) "I think I'll stick with SQLite's magical processing of mine" -- there, I fixed it for you :P > the mtime value is a floating point number, and we all know the

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Sep 5, 2012, at 3:03 PM, Richard Hipp wrote: >SELECT id, a, b, ..., min(mtime) >FROM tab >GROUP BY id >ORDER BY min(mtime) DESC; Perhaps something along these lines, i.e. a simple self-join: selectfoo.* from foo join ( select

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Am 05.09.2012 16:57, schrieb Richard Hipp: On Wed, Sep 5, 2012 at 10:53 AM, Olaf Schmidt wrote: Am 05.09.2012 15:58, schrieb Igor Tandetnik: Well, you could do something like this: SELECT id, a, b, ..., mtime FROM tab t1 where mtime = (select min(mtime) from tab t2 where

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Richard Hipp wrote: > In the actual use case that inspired this question (and for which, after > seeing the alternatives, I think I'll stick with SQLite's magical > processing of min()) the mtime value is a floating point number, and we all > know the hazards of comparing

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Wed, Sep 5, 2012 at 10:53 AM, Olaf Schmidt wrote: > Am 05.09.2012 15:58, schrieb Igor Tandetnik: > > > Well, you could do something like this: >> >> SELECT id, a, b, ..., mtime FROM tab t1 >> where mtime = (select min(mtime) from tab t2 where t2.id=t1.id) >> ORDER BY mtime

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Am 05.09.2012 15:58, schrieb Igor Tandetnik: Well, you could do something like this: SELECT id, a, b, ..., mtime FROM tab t1 where mtime = (select min(mtime) from tab t2 where t2.id=t1.id) ORDER BY mtime DESC; Ah, nice ... this solves the problem of the potential "non-uniqueness" of mtime...

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Richard Hipp wrote: > Suppose you have a table like this: > >CREATE TABLE tab(ID, A, B, C, ..., MTIME); > > The ID entries are not unique. For each ID occurrence, the values for A, > B, C, and so forth might (or might not) be different. We do queries like > this: > >

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Temporary table? Then you will have rowids. On Wed, Sep 5, 2012 at 4:16 PM, Richard Hipp wrote: > On Wed, Sep 5, 2012 at 9:12 AM, Baruch Burstein >wrote: > > > SELECT id, a, b, ..., mtime > > FROM tab > > WHERE rowid IN ( > > SELECT

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On 5 Sep 2012, at 2:03pm, Richard Hipp wrote: > *The problem to solve* is this: We want to restrict the output of the > query above to show only the entry with the smallest MTIME for each > distinct ID. > > As of SQLite version 3.7.11 >

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Wed, Sep 5, 2012 at 9:12 AM, Baruch Burstein wrote: > SELECT id, a, b, ..., mtime > FROM tab > WHERE rowid IN ( > SELECT id, min(mtime) > FROM tab > GROUP BY id > ) > ORDER BY mtime DESC; > In the actual application, "tab" is

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Sorry, that won't work. On Wed, Sep 5, 2012 at 4:12 PM, Baruch Burstein wrote: > SELECT id, a, b, ..., mtime > FROM tab > WHERE rowid IN ( > SELECT id, min(mtime) > > FROM tab > GROUP BY id > ) > ORDER BY mtime DESC; > > > On Wed,

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

SELECT id, a, b, ..., mtime FROM tab WHERE rowid IN ( SELECT id, min(mtime) FROM tab GROUP BY id ) ORDER BY mtime DESC; On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp wrote: > > SELECT id, a, b, ..., min(mtime) > FROM tab >

[sqlite] Finding rows with MIN(MTIME) for all IDs

Suppose you have a table like this: CREATE TABLE tab(ID, A, B, C, ..., MTIME); The ID entries are not unique. For each ID occurrence, the values for A, B, C, and so forth might (or might not) be different. We do queries like this: SELECT id, a, b, ..., mtime FROM tab ORDER BY