Offered up for anyone with time on their hands. I fiddled around with
this for half an afternoon, then gave up and did it programmatically in
Perl.
Given a table that looks something like this:
id | INTEGER
query| INTEGER
checksum | char(32)
score| INTEGER
include | BOOLEAN
The t
Jeff Boes <[EMAIL PROTECTED]> writes:
> I headed off in the direction of groups of SELECTs and UNIONs, and quit when I
> got to something like four levels of "SELECT ... AS FOO" ...
four? wimp, that's nothing!
ok, seriously I think there's no way to do this directly with straight SQL.
You would
On Thu, 2004-04-08 at 19:33, Greg Stark wrote:
> Jeff Boes <[EMAIL PROTECTED]> writes:
>
> > I headed off in the direction of groups of SELECTs and UNIONs, and quit when I
> > got to something like four levels of "SELECT ... AS FOO" ...
>
> four? wimp, that's nothing!
>
> ok, seriously I think t
Rod Taylor <[EMAIL PROTECTED]> writes:
> 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 ;-). There is no such thing in the
extant specs SQL92 or SQL99.
re
Rod,
> Something along the lines of the below would accomplish what you want
> according to spec. ROW_NUMBER() is a spec defined function. (6.10 of
> SQL200N)
Great leaping little gods! They added something called "row number" to the
spec?
Boy howdy, folks were right ... the ANSI committee r
Josh Berkus <[EMAIL PROTECTED]> writes:
> Rod,
>
> > Something along the lines of the below would accomplish what you want
> > according to spec. ROW_NUMBER() is a spec defined function. (6.10 of
> > SQL200N)
>
> Great leaping little gods! They added something called "row number" to the
> spe
On Fri, 2004-04-09 at 18:43, Greg Stark wrote:
> Josh Berkus <[EMAIL PROTECTED]> writes:
>
> > Rod,
> >
> > > Something along the lines of the below would accomplish what you want
> > > according to spec. ROW_NUMBER() is a spec defined function. (6.10 of
> > > SQL200N)
> >
> > Great leaping litt
-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
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
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
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
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
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
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
No, it will not work twice in the same query as is.
If you want to code two counter buckets and pass in
some way to distinguish between the two yada yada yada
it is possible. It is also possible to code this to
do multi-level counting/breaks/calculations, etc.
But the SD dictionary is by connect
Troels Arvin wrote:
See http://www.acm.org/sigmod/record/issues/0403/index.html#standards for
an article which summarizes the news in SQL:2003.
This is a very useful page; thank you for creating it and for noting it
in this thread!
--
(Posted from an account used as a SPAM dump. If you really wan
16 matches
Mail list logo
