Hi everyone,
first off thanks to everyone for replying to my answer!
On Sat, Dec 12, 2009 at 1:00 PM, <sqlite-users-requ...@sqlite.org> wrote:
> 7. Re: SQL question (Pavel Ivanov)
> 8. Re: SQL question (Rich Shepard)
> 10. Re: SQL question (Igor Tandetnik)
> 11. Re: SQL question (Simon Slavin)
> 12. Re: SQL question (Tim Romano)
> From: Rich Shepard <rshep...@appl-ecosys.com>
>> Schema of DB (simplified):
>> - Oper (string)
>> - Product (string)
>> - Category (string)
>> - Name (string)
>> - CreateTS (Timestamp)
>> - Value (Double)
> The schema refers to the set of tables, and the attributes within each
> table. Is the above one table or a set of tables.
Ok I should have been more specific: it's one table only (for historic
reasons as I've learned lately).
>> Primary keys are Oper, Product, Category, Name and CreateTS
> There is only one primary key per table.
So to say the primary key is "Oper, Product, Category, Name, CreateTS".
>
>> Can I do that in SQL in one query?
>
> I urge you to learn about database design and SQL. You can find a lot of
> information on the Web and there are many good books on each topic. Only one
> of the above attributes is specified as 'Double', yet you write about
> dumping data as doubles into each one. Step back and do some learning before
> you dig a big hole and fall into it.
Hehe.
Yeah thank you for pointing that out. Now I'll let my company know
this, then wI'll have a coffee with my team leader and laugh about it
a bit and then I'll go on digging my big hole. :-D
No seriously - and please don't take that as ad hominem because it
isn't - I need to get things running quickly. I do apologize if I got
some of the wording wrong and I do apologize if I could not get my
question very right the first time. But your "solution" does not help
me.
(And I'll point out some solution I got as PM which is working very
well and which I understood.)
>
> Message: 10
> Date: Fri, 11 Dec 2009 11:20:49 -0500
> From: "Igor Tandetnik" <itandet...@mvps.org>
> Subject: Re: [sqlite] SQL question
> To: sqlite-users@sqlite.org
> Message-ID: <hftrh1$t2...@ger.gmane.org>
> Content-Type: text/plain; charset="ISO-8859-1"
>
> Florian Schricker <fschric...@gmail.com>
> wrote:
>> 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
>>
>> 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.
>
> select * from mytable t1
> where oper=:oper and product=:product and category=:category and
> createTs = (select max(createTs) from mytable t2
> where t1.oper = t2.oper and t1.product = t2.product and t1.category =
> t2.category and t1.name = t2.name
> );
Works fine - thank you. I chose a similar, see below.
> From: Simon Slavin <slav...@bigfraud.org>
>> - CreateTS (Timestamp)
> No such type in SQLite. Take a look at
Ok this one seems to be some problem with me vs SQLite. I got the
create clause for the table from somebody else inside my company (some
DB2 guy) which is
CREATE TABLE MEASURE (
[...]
"CREATETS" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
[...]
and which was working fine to create the table. Just to clarify - what
has happened here? (I guess it is converted to text by SQLite but I'm
not sure.)
> From: Tim Romano <tim.rom...@yahoo.com>
> 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
Yes you are completely correct in what I'm trying to do. And thank you
for clarifying on the possibilities there are using joins.
But since I am atm only interested in full rows anyway I am running
select OPER, PRODUCT, CATEGORY, NAME, [...], VALUE, TEXT,
max(CREATETS) as CREATETS
from table
where oper = 'myOper' and product = 'myProduct' and Category = 'myCategory'
group by OPER, PRODUCT, CATEGOY, NAME order by CREATETS desc
which is working fine (and thank you Jeffrey Hoover for that.)
regards,
Florian
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
