a join between tables and a 'selective' update

	something numeric,
	something1 varchar,
	something2 integer references foreign key..., 
	... etc);
CREATE TABLE logfile2 (stamp date, when time,
	something varchar,
	something char[4],
	... etc);

        SELECT * FROM window ORDER BY tm;
...so that I can see the sequence of events irrespective of the 'logfile'
the event comes from.

Is there a simpler (but generic SQL) way to achieve the same output from given 'any logfile' set? One without an explicit creation of a transient WINDOW table?

ANOTHER closely related SQL problem is the ability to update just a few records of a table, records, which happen NOT to be distinguishable from one another to program making the query.

To explain.

Let's have a store, which hold food. One of the products is milk in cans. Every milk can has a unique (but random) serial number (ID) and validity date. When making a selection for a 'transporter' to shopping floor I'd like to select those that have earliest expiry date (are oldest). For the purpose of this problem, pls assume, that the timestamp MAY*NOT appear in query output. So, when shopping floor requests 10 cans, I mean something like:

        SELECT id FROM milk_store ORDER DESC BY expiry LIMIT 10;

(forgive me using the PostgreSQL LIMIT expression - but this is easies way to *present*the*problem) I'm actually looking for a generic SQL phrase, that would have the desired result (the above want work from PostgreSQL either, since the query may not be sorted by 'not displayed' column).

Then of course I'd like to be able to mark in database, which cans went out. Meaning something like:

	UPDATE milk_store SET state=sold WHERE id in (SELECT id FROM
	milk_store ORDER DESC BY expiry LIMIT 10);

Any help appreciated - particularly how to achieve above WITHOUT the LIMIT clause, which I understand is not quite so generic SQL.... note, that I'm searching for an SQL solution, not 'procedural SQL', which at this point is not quite so interesting for me.

Regards,
-R Received on Tue Jan 18 2005 - 16:47:50 CET