Can you see what I do wrong?
create table log(name,dt,time_in,time_out); insert into log values('Noname','2021-03-15','00:12','00:24'); select name, dt, time_in, time_out from log where dt > '2021-03-13' group by dt,time_in having max(time_out);
SELECT returns nothing when I expect one row.
If I comment out the
HAVING clause it works.
expression following HAVING must evaluate to a number which is interpreted as a boolean (True or False -- False is zero or null, and True is non-zero).
max(time_out) evaluates to '00:24' which, when converted to numeric, is zero or false.
sqlite> select cast('00:24' as numeric); ┌──────────────────────────┐ │ cast('00:24' as numeric) │ ├──────────────────────────┤ │ 0 │ └──────────────────────────┘ sqlite>
If I can divine your intention properly, what you probably mean is:
select name, dt, time_in, max(time_out) from log where dt > '2021-03-13' group by dt, time_in ;
But what if my
SELECT did not include the column whose
max() I wanted?
What if I needed to return only the
time_in from the group row that has the maximum
Is there some way for
HAVING to match the row with the lexicographically higher string value without resorting to more complicated solutions using sub-queries?
Never mind, I figured it out.
HAVING time_out = max(time_out)
(5) By Gunter Hick (gunter_hick) on 2021-03-15 14:58:18 in reply to 4 [link] [source]
Note that this works only for SQLite. And even then, if you have multiple entries with identical dt, time_in and time_out fields (with time_out == max(time_out) from the (dt, time_in) group), then SQLite may return any one of the name values.
The standard way would be to select what you want. An example that should work in anything that understands generic SQL might be:
select ( select name from log where dt == o.dt and time_in == o.time_in and max_out == o.max_out ) as name, dt, time_in from ( select dt, time_in, max(time_out) as max_out from log where dt > '2021-03-13' group by dt, time_in ) as o ;