SELECT id, MAX(probe_time) AS Expr1 FROM app_states GROUP BY logon_id
SELECT id, MIN(probe_time) AS Expr1 FROM app_states GROUP BY logon_id
I wish to select row with MAX or MIN time for every user.
I'm afraid you'll have to use sub queries here.
SELECT id, probe_time FROM app_states WHERE probe_time = (SELECT MAX(probe_time) from app_states GROUP BY logon_id)
BTW, SQLite does interesting optimization on MIN/MAX:
Queries of the following forms will be optimized to run in logarithmic time assuming appropriate indices exist:
SELECT MIN(x) FROM table; SELECT MAX(x) FROM table;
In order for these optimizations to occur, they must appear in exactly the form shown above - changing only the name of the table and column. It is not permissible to add a WHERE clause or do any arithmetic on the result. The result set must contain a single column. The column in the MIN or MAX function must be an indexed column.
idbut grouping by
logon_id- bernie 2012-04-03 20:44