[SQL] trigger/for key help

2004-04-10 Thread Bret Hughes
S*t s*t s*t. I have managed to screw up the system tables trying to delete a foreign key on a new table I was going to start using tomorrow. elevating-# \d diag_logs Table "diag_logs" Column | Type | Modifiers

Re: [SQL] SQL challenge--top 10 for each key value?

2004-04-10 Thread Greg Stark
elein <[EMAIL PROTECTED]> writes: > create or replace function pycounter(integer) > returns integer as > ' >if args[0] == 0: > SD["nextno"] = 1 > return SD["nextno"] >try: > SD["nextno"] += 1 >except: > SD["nextno"] = 1 >return SD["nextno"] > ' language 'pl

Re: [SQL] SQL challenge--top 10 for each key value?

2004-04-10 Thread elein
This solution will be in Monday's edition of PostgreSQL General Bits (http://www.varlena.com/GeneralBits). (In other words, if it doesn't do what you mean, let me know now!) CREATE TYPE topscores AS (id integer, query integer, checksum char(32), score integer); CREATE OR REPLACE FUNCTION tops

Re: [SQL] SQL challenge--top 10 for each key value?

2004-04-10 Thread elein
Welcome to the real world, Josh. There are people who have full time salaried positions soley to attend standards meetings. Note that ROW_NUMBER() really is handy, regardless of the silly name. And there was a little python function of mine that did it fairly simply, except that you needed to

Re: [SQL] SQL challenge--top 10 for each key value?

2004-04-10 Thread Troels Arvin
On Sun, 11 Apr 2004 00:13:17 +0200, I wrote: > Among its new (non-core) OLAP features are a set of > "windows functions" Sorry - I meant "window functions"... (Microsoft don't seem to have had much influence in SQL:2003's OLAP-specifications; IBM seems to be the big influencer in those parts of t

Re: [SQL] begin update ... syntax error

2004-04-10 Thread Adrian Klaver
On Saturday 10 April 2004 02:32 pm, Kemin Zhou wrote: begin; Note the semi-colon Then the update query; commit; or rollback; > I was trying to speed up a simple update query > > fri=# begin > fri-# update tabA set nobegin=tmp.nobegin, noend=tmp.noend > fri-# from tmp > fri-# where tabA.acc=tmp.acc;

Re: [SQL] SQL challenge--top 10 for each key value?

2004-04-10 Thread Troels Arvin
On Fri, 09 Apr 2004 02:11:44 -0400, Tom Lane wrote: >> ROW_NUMBER() is a spec defined function. (6.10 of SQL200N) > > If the spec doesn't even have a year number yet, you can hardly expect > real implementations to support it ;-) SQL:2003 is finished. Among its new (non-core) OLAP features are a

Re: [SQL] begin update ... syntax error

2004-04-10 Thread Gregory S. Williamson
try a semicolon after the begin ? begin; commit; Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Kemin Zhou [mailto:[EMAIL PROTECTED] Sent: Sat 4/10/2004 2:32 PM To: [EMAIL PROTECTED] Cc: Subject:[SQL] begin update ... syntax error I was trying to spe

[SQL] begin update ... syntax error

2004-04-10 Thread Kemin Zhou
I was trying to speed up a simple update query fri=# begin fri-# update tabA set nobegin=tmp.nobegin, noend=tmp.noend fri-# from tmp fri-# where tabA.acc=tmp.acc; ERROR: syntax error at or near "update" at character 7 The same query can be run with no problem if not starting with BEGIN. Does mea

Re: [SQL] SQL challenge--top 10 for each key value?

2004-04-10 Thread Josh Berkus
Rod, Greg > It's not really like Oracles row num at all, though I suppose you can > emulate rownum using it. The intention is that you will use it for > "aggregates" like running totals, moving averages, counting, etc. Yes, that makes a certain amount of sense. I just take exception to the name

Re: [SQL] setting a non-standard date format for the duration of a session

2004-04-10 Thread Josh Berkus
Cris, > I understand I can use the function to_date(s,f) for converting a string > into a date using a non-standard format. > > I'm wondering if there is a way to set a non-standard date format for > the duration of a session, something like the "ALTER SESSION SET > NLS_DATE_FORMAT='f'" Oracle s

Re: [SQL] hi sir urgent..required a Query

2004-04-10 Thread Josh Berkus
Rajeev, > I am attaching a document with this file showing two table.. < you.doc>> > > It consist of two table VALUE AND VALUE_TYPE.. > > when i have value_type_id=1 i must get Value_varchar column value > > when i have value_type_id=2 i must get Value_int column value > Please send a SQL query fo

Re: [SQL] Counting rows from two tables in one query

2004-04-10 Thread Bruno Wolff III
On Wed, Apr 07, 2004 at 10:06:20 +, Stefan Weiss <[EMAIL PROTECTED]> wrote: > Hi. > > What I am trying to get is a list that shows how many records from > 'sub_a' and 'sub_b' are referencing 'main': > > main_id | count_a | count_b > -+-+- > 1 | 2 | 1 >

Re: [SQL] SQL challenge--top 10 for each key value?

2004-04-10 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > How would you go about getting the top N (say, the top 10) for each query? Assume you have a table "ch" and three sequences 'aa', 'bb', and 'cc'. (Only 'aa' and 'bb' need to be initially set) SELECT setval('aa',1,'f'); SELECT setval('bb',1,'f