On 1/11/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Martin O'Leary <[EMAIL PROTECTED]> wrote: > > Hi guys, > > > > I have a table like the following: > > > > CREATE TABLE user_actions ( > > uid INTEGER NOT NULL, > > actionid INTEGER NOT NULL, > > time INTEGER NOT NULL, > > status INTEGER NOT NULL, > > PRIMARY KEY (uid, actionid, time, status) > > ); > > > > And I want to carry out a query something like this: > > > > SELECT uid, actionid, MAX(time), status FROM user_actions GROUP BY > > uid, actionid; > > Are you thinking that the value of "status" returned will > be the one which has the maximum value for "time"? SQL doesn't > work that way. To understand why not, consider this query:
Ah, yes. I took over this SQL from someone else and hadn't really thought about its correctness, merely its speed. Thanks for pointing it out. > All data is duplicated - it appears in both the table and in the > index. There is no way around that. > Wasn't really expecting it, but thought it couldn't hurt to ask. > Repeat until done. Runtime is O(MlogN) where N is the number > of rows in the table and M is the number of rows of output. > Since M is likely much less than N, this approach will be much > faster. Yes, that's more or less the way I'd optimised the query in my head, and I was hoping there was a way to force SQLite to do things that way. I guess I'll just implement it in the application. Thanks for your help. Also, with regard to 3.3.0, the alpha release seems to slow down my example query by about 20% (The virtual machine opcodes are identical). Is this a bug? Thanks again, Martin
