expression after group by has same result

Go To StackoverFlow.com

0

Why query:

SELECT id, MAX(probe_time) AS Expr1
FROM app_states
GROUP BY logon_id

and

SELECT id, MIN(probe_time) AS Expr1
FROM app_states
GROUP BY logon_id

has same id result.

I wish to select row with MAX or MIN time for every user.

2012-04-03 20:33
by userbb
why are you selecting id but grouping by logon_id - bernie 2012-04-03 20:44
this is subquery, and I want to use id for later joins. I want to select with linq query like above and use id for joins - userbb 2012-04-03 20:47


2

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.

http://www.sqlite.org/optoverview.html#minmax

2012-04-03 20:48
by greut