select col value where another col is max or min-CodePudding

I have a table that looks like this:

user      timestamp1                     timestamp2
a         2020-10-07T15:36:01.323Z       2020-10-07T15:36:01.323Z
b         2020-10-07T15:36:01.323Z       2020-10-07T15:36:01.323Z

I can calculate the max or min time difference like this:

min((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) AS min
max((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) AS max

but how can I formulate an sql (sqlite) query such that I can extract the username the min max value in a table?

The final result could look like this:

type    user     time
max     a        679..
min     b        12..

I tried something like this to just test:

"select user, ((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) AS val from mytable where ((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) == min((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24)

I was hoping that this will return the username and the min value throughout, something like this:

user    val
a       12...

but I get an error that:

PandaSQLException: (sqlite3.OperationalError) misuse of aggregate function min()

CodePudding user response：

The simplest solution would be to use MIN() and MAX() aggregate functions in 2 separate queries and UNION ALL for the results:

SELECT 'max' type, user, MAX((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) time FROM tablename
UNION ALL
SELECT 'min' type, user, MIN((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) time FROM tablename

This query uses SQLite's feature of bare columns.

I would also suggest to use strftime() instead of JULIANDAY(), because it would provide better accuracy:

SELECT 'max' type, user, MAX((strftime('%s', timestamp2) - strftime('%s', timestamp1)) / 3600.0) time FROM tablename
UNION ALL
SELECT 'min' type, user, MIN((strftime('%s', timestamp2) - strftime('%s', timestamp1)) / 3600.0) time FROM tablename

See the demo.