Re: [SQL] Deleting obsolete values

[Pat M]

Delete from partitur where userid NOT IN (SELECT DISTINCT ON (userid)
userid, val, ts FROM partitur)

"Haller Christoph" <[EMAIL PROTECTED]> wrote in message
200110161445.QAA11833@rodos">news:200110161445.QAA11833@rodos...
> This may look familiar to you - it was on the list last month.
> Consider the following table
> create table partitur
>  (userid text, val integer, ts timestamp DEFAULT NOW() );
> Do some inserts
> insert into partitur values('Bart', 1440);
> insert into partitur values('Lisa', 1024);
> insert into partitur values('Bart', 7616);
> insert into partitur values('Lisa', 3760);
> insert into partitur values('Bart', 3760);
> insert into partitur values('Lisa', 7616);
> To retrieve the latest values (meaning the last ones inserted)
> Tom Lane wrote
> >This is what SELECT DISTINCT ON was invented for.  I don't know any
> >comparably easy way to do it in standard SQL, but with DISTINCT ON
> >it's not hard:
> >SELECT DISTINCT ON (userid) userid, val, ts FROM partitur
> >ORDER BY userid, ts DESC;
>
> My question now is
> Is there a way to delete all rows the select statement did not
> bring up?
> After that *unknown* delete statement
> select userid, val, ts from partitur ;
> should show exactly the same as the SELECT DISTINCT ON (userid) ...
> did before.
>
> Regards, Christoph
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Function return rows?

[Pat M]

Can I use a function to execute a query and return a row or set of rows? If
so, can you point me to some examples or perhaps give me an example of a
function that would do roughly the same thing as:

select * from mytable where mytable.name ~* 'aname';

I can't seem to find any examples that return rows, just single ints and
bools and stuff.





---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Cenceptual help needed - periodic events

[Pat M]

I'm goofing around, designing a planned maintenance system. In a couple of
weeks I'll be taking on the actual scheduling of tasks. How would you
experts out there approach this? I've beeen thinking I can approach this two
ways.

1) When a list of tasks is requested, generate new rows and store them.

2) When a list of tasks is requested, look at the task row and calculate the
tasks, display them, but do not generate rows for storage. Just calc on the
fly all the time.

Method #1 would mean less CPU and RAM use but then I'd have to worry about
tasks changing or being added to a maintenance schedule and not being
reflected in any pre-generated rows. #2 would alleviate that problem, but
may be slower overall and perhaps eat large amounts of ram.

If anyone has done something similar with periodic events, what did you find
worked best?

I'm planning on using a base task record to record the frequency and other
specifics of a task. This record is the basis for generating the periodic
tasks.

One row:
task_id =1 (serial)
task_system = 1
task_equip =  12
task_text = Lubricate fan shafts with lithium grease
task_interval = 1 month (using an interval type column)
task_startmonth = 2 (offset so that not ALL 6 month interval tasks actuall
happen in June)

Another row:
task_id =25 (serial)
task_system = 8
task_equip =  72
task_text =
task_interval = 6 month (using an interval type column)
task_startmonth=3

Then I'll have to step through the calendar by month and match up all the
task rows and print out a single page for each system.

This is all a brand new concept to me, so any suggestions are more than
welcome. I still have a few weeks work in other areas to keep me busy, but
this is the toughy that I'm spending brain time on =)




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly