Can you see what I do wrong?
(1) By anonymous on 2021-03-15 00:46:50
``` 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); ``` The above `SELECT` returns nothing when I expect one row. If I comment out the `HAVING` clause it works.
(2.1) By Keith Medcalf (kmedcalf) on 2021-03-15 01:09:48 edited from 2.0 in reply to 1 [link]
The `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). The expression `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 ; ```
(3) By anonymous on 2021-03-15 14:28:04 in reply to 2.1 [link]
OK, thanks. But what if my `SELECT` did not include the column whose `max()` I wanted? What if I needed to return only the `name`, `dt`, and `time_in` from the group row that has the maximum `time_out` value? 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?
(4) By anonymous on 2021-03-15 14:43:33 in reply to 3 [link]
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]
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. See https://sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause
(6) By Keith Medcalf (kmedcalf) on 2021-03-15 15:12:24 in reply to 3 [link]
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 ; ```