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

[Wiktor Adamski]

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 was my fault and teradata is not guilty at all - they 
still allow to write it easier than standard:
select * from tab qualify row_number() over(partition by id order by 
mtime) = 1

or if you prefer:
select * from tab qualify lag(0, 1, 1) over(partition by id order by 
mtime) = 1



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

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, 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
  fromfoo
)
select  *
fromDataSet

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Wiktor Adamski]

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)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

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 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).

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BardzoTajneKonto]

select * from tab
id | a  | mtime | 
+--++---+--
 1 |  1 | 3 | 
 1 |  2 | 2 | 
 1 |  3 | 4 | 
 1 |  4 | 2 | 
 1 |  5 | 7 | 
 2 |  6 | 1 | 
 2 |  7 | 0 | 
 2 |  8 | 4 | 
 2 |  9 | 1 | 
 2 | 10 | 2 | 

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
id | a | m | 
+--+---+---+--
 1 | 2 | 2 | 
 2 | 7 | 0 |


qualify isnt't in standad. instead subquery can be used.


 
> Message: 14
> Date: Wed, 5 Sep 2012 09:03:35 -0400
> From: Richard Hipp 
> 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);
> 
> 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 mtime DESC;
> 
> *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
> the problem can be
> solved like this:
> 
> SELECT id, a, b, ..., min(mtime)
> FROM tab
> GROUP BY id
> ORDER BY min(mtime) DESC;
> 
> SQLite, unique among SQL database engines as far as I know, will choose for
> A, B, C, ... the values of those columns that correspond to the row that
> satisfies the min(mtime) aggregate.  And so this actually works.  On SQLite
> versions prior to 3.7.11, the values of A, B, C, .. would be from any
> arbitrary row having the corresponding ID.  And on more strictly conforming
> SQL implementations (ex: PostgreSQL) the query will not even parse because
> the GROUP BY clause does not contain every column that is not within an
> aggregate function.  But the query does work, and works efficiently and
> well, in SQLite and seems very convenient.
> 
> *Question*:  Is there a (better) standard SQL way to do the same thing?
> 
> 
> 
> *Context*:  The actual application here is Fossil, and the display of all
> changes to a particular file in a repository.  Queries like the above are
> used to generate, for example, a complete history of the changes to the
> date/time function logic in SQLite:
> 
>  http://www.sqlite.org/src/finfo?name=src/date.c
> 
> In this example, the "ID" column corresponds to a particular version (SHA1
> hash) of a file.  That same version might appear in various branches and so
> there are duplicate entries in the graph for the same file, which can be
> confusing.  (Note, the actual
> queryto
> render the "finfo" page is slightly more complex; "tab" is really a
> join
> of two tables, and the "a, b, c, .." columns are subqueries.  But these
> complications do not bare upon the question of this email.)  To work around
> the confused output, we'd like to only see a single row for each ID,
> specifically the row with the smallest value of mtime, which means the
> first time that a particular version of a file appears in the repository.
> For example:
> 
> http://www.sqlite.org/src/finfo?name=src/date.c=1
> 
> The recent change to Fossil to add the "fco" query parameter to the "finfo"
> page is the first time I have actually needed SQLite's min()/max() query
> behavior myself, and I now find it *very* convenient.  But, I am slightly
> troubled by depending on SQL behavior that is only found in SQLite in not
> in other database engines.
> -- 
> D. Richard Hipp
> d...@sqlite.org

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

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 hazards of 
> comparing floating point numbers for equality, right?

As pointed out by Igor & Olaf already… why would that ever be an issue in that 
specific scenario?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

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  (
selectid,
  min( mtime ) as mtime
from  foo
  
group by  id
  )
asMinFoo
onMinFoo.id = foo.id
and   MinFoo.mtime = foo.mtime

order by  foo.mtime desc


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Olaf Schmidt]

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 t2.id=t1.id)
ORDER BY mtime DESC;



Ah, nice ... this solves the problem of the potential
"non-uniqueness" of mtime... (was fiddeling along with
something like that too, but so far without a result).

Just for completeness (in case mtime *is* unique):


SELECT id, a, b, ..., mtime FROM tab
WHERE mtime IN (SELECT Min(mtime) FROM tab GROUP BY id)
ORDER BY mtime DESC



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())


Sure, I've always seen this special Grouping-behaviour of SQLite
as a feature (when no "SQL-portability" needed to be considered).


the mtime value is a floating point number, and we all
know the hazards of comparing floating point numbers for equality, right?


Hmm, out of interest, considering that in this case - where the
query doesn't contain any Float/String-conversion and no dynamic
calculations (just plain Byte-by-Byte comparison of the stored
SQLite.Values?) - would it really be that risky?


Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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 floating point numbers for equality, right?

This applies when the floating point number is the result of calculations. If 
you just read a number twice from the same source, the two copies should 
compare equal just fine.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 t2.id=t1.id)
>> ORDER BY mtime DESC;
>>
>
> Ah, nice ... this solves the problem of the potential
> "non-uniqueness" of mtime... (was fiddeling along with
> something like that too, but so far without a result).
>
> Just for completeness (in case mtime *is* unique):
>
>
> SELECT id, a, b, ..., mtime FROM tab
> WHERE mtime IN (SELECT Min(mtime) FROM tab GROUP BY id)
> ORDER BY mtime DESC
>

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 floating point numbers for equality, right?


>
> Olaf
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Olaf Schmidt]

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... (was fiddeling along with
something like that too, but so far without a result).

Just for completeness (in case mtime *is* unique):

SELECT id, a, b, ..., mtime FROM tab
WHERE mtime IN (SELECT Min(mtime) FROM tab GROUP BY id)
ORDER BY mtime DESC

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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:
> 
>SELECT id, a, b, ..., mtime
>FROM tab
>ORDER BY mtime DESC;
> 
> *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.
> 
> *Question*:  Is there a (better) standard SQL way to do the same thing?

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;

or

SELECT id, a, b, ..., mtime FROM tab t1
where not exists (select 1 from tab t2 where t2.id = t1.id and t2.mtime > 
t1.mtime)
ORDER BY mtime DESC;

I believe these queries are standard, but are likely to be much slower than the 
original.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Baruch Burstein]

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 id, min(mtime)
> > FROM tab
> > GROUP BY id
> > )
> > ORDER BY mtime DESC;
> >
>
> In the actual application, "tab" is not a real table but is a join:
>
>  http://www.fossil-scm.org/fossil/artifact/5dfd626877f?ln=271-273
>
> So there isn't a ROWID available to select from.
>
>
> >
> > On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp  wrote:
> >
> > >
> > > SELECT id, a, b, ..., min(mtime)
> > > FROM tab
> > > GROUP BY id
> > > ORDER BY min(mtime) DESC;
> >
> >
> >
> >
> > --
> > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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
> the problem can be
> solved like this:
> 
>SELECT id, a, b, ..., min(mtime)
>FROM tab
>GROUP BY id
>ORDER BY min(mtime) DESC;
> 
> SQLite, unique among SQL database engines as far as I know, will choose for
> A, B, C, ... the values of those columns that correspond to the row that
> satisfies the min(mtime) aggregate.  And so this actually works.  On SQLite
> versions prior to 3.7.11, the values of A, B, C, .. would be from any
> arbitrary row having the corresponding ID.  And on more strictly conforming
> SQL implementations (ex: PostgreSQL) the query will not even parse because
> the GROUP BY clause does not contain every column that is not within an
> aggregate function.  But the query does work, and works efficiently and
> well, in SQLite and seems very convenient.
> 
> *Question*:  Is there a (better) standard SQL way to do the same thing?

For the sake of completeness, I supply an answer you may reject: do it in two 
SELECTs.  The first one just identifies something unique about the row which 
has the entry with the smallest mtime -- perhaps the mtime value itself if 
that's unique and indexed, otherwise rowid.  The second one gets the values of 
a, b, c, etc. from that row.  Will work for all SQL engines, and is not 
dependent on obscure (even if documented) behaviour of GROUP BY ORDER BY.

I've noticed that the majority of people posting here seem to prefer one long 
complicated query which requires detailed knowledge of SQL syntax to two simple 
queries.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 not a real table but is a join:

 http://www.fossil-scm.org/fossil/artifact/5dfd626877f?ln=271-273

So there isn't a ROWID available to select from.


>
> On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp  wrote:
>
> >
> > SELECT id, a, b, ..., min(mtime)
> > FROM tab
> > GROUP BY id
> > ORDER BY min(mtime) DESC;
>
>
>
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Baruch Burstein]

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, Sep 5, 2012 at 4:03 PM, Richard Hipp  wrote:
>
>>
>> SELECT id, a, b, ..., min(mtime)
>> FROM tab
>> GROUP BY id
>> ORDER BY min(mtime) DESC;
>
>
>
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
>


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Baruch Burstein]

 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
> GROUP BY id
> ORDER BY min(mtime) DESC;




-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 mtime DESC;

*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
the problem can be
solved like this:

SELECT id, a, b, ..., min(mtime)
FROM tab
GROUP BY id
ORDER BY min(mtime) DESC;

SQLite, unique among SQL database engines as far as I know, will choose for
A, B, C, ... the values of those columns that correspond to the row that
satisfies the min(mtime) aggregate.  And so this actually works.  On SQLite
versions prior to 3.7.11, the values of A, B, C, .. would be from any
arbitrary row having the corresponding ID.  And on more strictly conforming
SQL implementations (ex: PostgreSQL) the query will not even parse because
the GROUP BY clause does not contain every column that is not within an
aggregate function.  But the query does work, and works efficiently and
well, in SQLite and seems very convenient.

*Question*:  Is there a (better) standard SQL way to do the same thing?



*Context*:  The actual application here is Fossil, and the display of all
changes to a particular file in a repository.  Queries like the above are
used to generate, for example, a complete history of the changes to the
date/time function logic in SQLite:

 http://www.sqlite.org/src/finfo?name=src/date.c

In this example, the "ID" column corresponds to a particular version (SHA1
hash) of a file.  That same version might appear in various branches and so
there are duplicate entries in the graph for the same file, which can be
confusing.  (Note, the actual
queryto
render the "finfo" page is slightly more complex; "tab" is really a
join
of two tables, and the "a, b, c, .." columns are subqueries.  But these
complications do not bare upon the question of this email.)  To work around
the confused output, we'd like to only see a single row for each ID,
specifically the row with the smallest value of mtime, which means the
first time that a particular version of a file appears in the repository.
For example:

http://www.sqlite.org/src/finfo?name=src/date.c=1

The recent change to Fossil to add the "fco" query parameter to the "finfo"
page is the first time I have actually needed SQLite's min()/max() query
behavior myself, and I now find it *very* convenient.  But, I am slightly
troubled by depending on SQL behavior that is only found in SQLite in not
in other database engines.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users