On Jun 12, 2009, at 3:46 PM, Allen Fowler wrote:
> sqlite>
> select *, min((strftime('%s', end) - strftime('%s', start))) as
> length
> from
> ...> events where
> ...> start < datetime('now', '+1 day','start of day',
> '+9 hours','+30 minutes')
> ...> and end > datetime('now', '+1 day','start
> of day', '+9 hours','+30 minutes')
> ...> group by name;
>
> id name kind
> start end length
> -------------------- -------------------- --------------------
> -------------------- -------------------- ----------
> 3 joe hour
> 2009-06-13 09:00:00 2009-06-13 10:00:00 3600
> 4 tom day
> 2009-06-13 00:00:00 2009-06-14 00:00:00 3600
> sqlite>
>
> However this result returned is very wrong. The length col is
> correct but the other cols for "tom" are wrong. (It should be "2 |
> tom | hour | 2009-06-13 09:00:00 | 2009-06-13 10:00:00 | 3600" )
>
> What am I missing here? Am I doing the query wrong?
Yes. The "group by" doesn't know which rows to use for columns that
are not either aggregate functions (such as min) or grouped columns
(such as name). You know what min() does, but the query processor
doesn't.
You can do this in two steps by getting the min time length per name,
and joining that with the original table augmented with length.
e
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
