Can you see what I do wrong?

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

```
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

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
;
```