Can you see what I do wrong?

(1) By anonymous on 2021-03-15 00:46:50 [link] [source]

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] [source]

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 [source]

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] [source]

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.

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] [source]

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
;