It doesn't seem to work that way. I have 192330 distinct dttm entries, but I still only get 1 row. It seems as the “date(dttm)” result is either null or the empty string for all records. Not sure why. This works in other databases, so there must be something subtle about the “date()” function or the “dttm” column that im missing here.
sqlite> select date(dttm) as dt,max(used_kb)/1024.0/1024.0 from foo group by dt order by 1; |5038.83195495606 sqlite> select count(distinct dttm) from foo; 192330 sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, March 22, 2018 4:21 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] How to convert a datetime column to a date? On 2018/03/21 5:30 PM, Ron Watkins wrote: > I have a table which contains a datetime column: > > > > table|foo|foo|2|CREATE TABLE foo ( > > dttm datetime not null > > i int not null > > ) > > > > I want to select out the max(i) value for each day where there are > multiple records per day. > > > > select date(dttm) dt,max(i) from foo group by dt order by 1; > > > > However, it’s returning only 1 row, with no date column shown. How can > I get an actual “date” listed in the first column, and also get 1 row > per “date” value. I fear I might be missing something obvious, but wouldn't this just do the job?: SELECT date(dttm) AS dt, max(i) FROM foo GROUP BY date(dttm) If you still get only 1 row, it means there is only 1 date value in all the rows in your table. _______________________________________________ sqlite-users mailing list <mailto:sqlite-users@mailinglists.sqlite.org> sqlite-users@mailinglists.sqlite.org <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
