> So for a set of measurements identified by everything but their name > select the latest rows as defined by the timestamp. > > Can I do that in SQL in one query?
Maybe this is what you want: select Name, Value, CreateTS from TableName where Oper = 'op' and Category = 'cat' and Product = 'product' order by CreateTS desc limit 10; Constant in the "limit" clause depends on how many "latest rows" do you need. Pavel On Fri, Dec 11, 2009 at 8:13 AM, Florian Schricker <fschric...@gmail.com> wrote: > Hi everyone! > > > I'm a starter on SQL / SQLite and there is some problem I'd solve in > software but I have the feeling this can be done using a query. If > somebody can help me out I'd be glad - I have the feeling there is > something to learn for me here. > > Here goes: > > Schema of DB (simplified): > - Oper (string) > - Product (string) > - Category (string) > - Name (string) > - CreateTS (Timestamp) > - Value (Double) > > Primary keys are Oper, Product, Category, Name and CreateTS > > Basic usage is dumping "measurements" as doubles into the table. This > happens usually in larger groups like > > - "op", "cat", "product", "m1", 1.234, 2009-11-24 09:49:20.25 > - "op", "cat", "product", "m2", 1.234, 2009-11-24 09:49:20.25 > - "op", "cat", "product", "m3", 1.234, 2009-11-24 09:49:20.25 > - "op", "cat", "product", "m4", 1.234, 2009-11-24 09:49:20.25 > > Now suppose this has happend a lot so that m1 to m4 is present quite > often but of course with differing timestamps in CreateTS (it's a > primary key after all). > > What I could not figure out: Suppose I want to select rows by "op", > "cat", "product" and get all measurements "m1" to "m4" with the latest > timestamp in CreateTS. > > So for a set of measurements identified by everything but their name > select the latest rows as defined by the timestamp. > > Can I do that in SQL in one query? > > Any help is very much appreciated! (I know this might be a simple SQL > question for most here and I should better get some training on all > that. But if somebody can help a bit with that I'd sure be glad!) > > > kind regards > Florian > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
