If I understand your question correctly, Florian, you want the most
recent Value for each entity represented by the composite primary key
{oper, product, category, name}.
To find the rows that contain the most recent values (although not yet
the values themselves) you first need to aggregate by the composite key
and then use an aggregate max() function to find the latest (biggest)
value for the timestamp column for each aggregation:
select oper, product, category, name, max(CreateTS) as LatestTimestamp
from yourTable
group by oper, product, category, name
The query above returns the rows that contain the most recent
measurement but does not return the value itself. To get your values,
you can join your table again to the query above, representing the query
above as an inline view ( a set of data or a "relation"):
select oper, product, category, name, value
from yourTable as T
JOIN
(
select oper, product, category, name, max(CreateTS)
from yourTable
group by oper, product, category, name
) as MostRecent
on T.oper = MostRecent.oper
and T.product=MostRecent.product
and T.category=MostRecent.category
and T.name=MostRecent.name
and T.CreateTS = MostRecent.LatestTimeStamp
order by T.oper, T.product, T.category, T.name
NOTE, that this approach assumes the data in column CreateTS is a string
and always follows the format:
2009-11-24 09:49:20.25
YYYY-MM-DD HR:MN:SECONDS.HUNDREDTHS
Regards
Tim Romano
Florian Schricker 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
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.426 / Virus Database: 270.14.103/2558 - Release Date: 12/11/09
> 10:06:00
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
