Allen Fowler wrote:
>
>
>
>> You could be storing event duration, not stop time. Or perhaps store
>> both.
>>
>>
>
> Here is what I have so far:
>
> sqlite> create table events (id INTEGER PRIMARY KEY AUTOINCREMENT, name,
> kind, start, end);
>
> # Now add some events for "tomorrow"
> sqlite>
> insert into events values (null, 'tom', 'hour', datetime('now', '+1
> day','start of day', '+11 hours'), datetime('now', '+1 day','start of
> day', '+12 hours'));
> sqlite> insert into events values (null,
> 'tom', 'hour', datetime('now', '+1 day','start of day', '+9 hours'),
> datetime('now', '+1 day','start of day', '+10 hours'));
> sqlite>
> insert into events values (null, 'joe', 'hour', datetime('now', '+1
> day','start of day', '+9 hours'), datetime('now', '+1 day','start of
> day', '+10 hours'));
>
> # Now add an all-day event for tomorrow. (It overlaps a couple of above
> events.)
> sqlite> insert into events values (null,
> 'tom', 'day', datetime('now', '+1 day','start of day'), datetime('now',
> '+1 day','start of day', '+1 day'));
>
> # Show all events and duration:
> sqlite> select *, (strftime('%s', end) - strftime('%s', start)) as length
> from events;
> id name kind start
> end length
> -------------------- -------------------- --------------------
> -------------------- -------------------- ----------
> 1 tom hour 2009-06-13
> 11:00:00 2009-06-13 12:00:00 3600
> 2 tom hour 2009-06-13
> 09:00:00 2009-06-13 10:00:00 3600
> 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 86400
>
>
> #
> # And now, I want to get a result table with one row per user showing the
> "shortest active at 9:30 AM event" for each user.
>
> 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?
>
>
Allen,
Yes, you are doing something wrong. It is probably the most common
mistake made when using grouping in SQL.
When grouping, keep in mind that all columns that appear in your SELECT
column list, that are not aggregated (used along with one of the SQL
aggregate functions), have to appear in the GROUP BY clause too.
The only valid columns in your output (i.e. with a select *, min()) is
the column named in the group by clause (i.e. the name) and the
aggregate value (i.e. the min()). You have determined the length of the
minimum event for each name that meets your other conditions and nothing
else. You do not know its id, what kind it is, or when it starts or
ends. The values shown for those other fields are randomly selected from
the set of rows in the matching group.
SQLite and some other database programs don't complain when you to break
this rule because it is sometimes useful to get a randomly selected
value for a column in the group. Usually it just leads to the confusion
you are seeing.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
