[SQL] Deleting in order from a table

[pginfo]

Hi ,

I think the question is stupied, but it is importand for me.

I have a table tableA ( ,order_num int).

I will to delete some records from tableA but in asc or desc
order_num-order.

Is it possible to write delete from tableA where (some conditions) order
by order_num ?

Many thanks,
ivan.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] weighting (the results of) a query ?

[Peter Galbavy]

I have a table with a primary key ('md5') and a bunch of text fields.
There is one row per 'photograph' and the number of rows is about 1100
now but will rise to over 20,000 in a few months - assuming I get time
to import all my stuff.

I would like to offer users on my web site a free text search on these
text fields, but I would like to weight the results base on which field
the text came from.

Let's say those fields are (for simplicity) 'category', 'subcategory',
'caption' and 'keywords'.

I want to do:

SELECT md5, weighting() FROM images WHERE
category ~* 'term' OR subcategory ~* 'term' OR ...

Is there anything I can do - including writing functions - to return a
number that is somehow representative of which WHERE clause matched
'first' and even better the more columns matched ?

I am guessing that like 'C' an 'OR' conditional stops at the first match
and does not process further conditions after a previous one has
matched - that's good enough for me for day one...

It is not critial that I get a value out, the return order of results
could be fine too.

I would like to minimise the number of queries to the DB, but I can fall
back on doing one query per column and combining the results in perl.
This is my approach for an initial implementation later today unless
anyone can suggest otherwise...

Any pointers, tips, code, suggestions greatly appreciated.

Happy New Year all, BTW
--
Peter


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

Re: [SQL] Deleting in order from a table

[Ludwig Lim]

--- pginfo <[EMAIL PROTECTED]> wrote:
> I have a table tableA ( ,order_num int).
> 
> I will to delete some records from tableA but in asc
> or desc
> order_num-order.
> 
> Is it possible to write delete from tableA where
> (some conditions) order
> by order_num ?
> 
> Many thanks,
> ivan.
> 

Try the following:

   DELETE
   FROM tableA
   WHERE order_num IN (
SELECT order_num
FROM tableA
ORDER BY order_num
LIMIT n OFFSET m);  

 Use LIMIT to determine the number of rows to delete
and OFFSET to determine the "starting row".

ludwig


__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

---(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