2009/4/9 Віталій Тимчишин :
> OK, got to my postgres. Here you are:
>
> create or replace function explode_array(in_array anyarray) returns setof
> anyelement as
> $$
> select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
> $$
> language sql immutable;
>
in 8.4, this will be replace
On Tue, Apr 14, 2009 at 5:33 AM, Matthew Wakeling wrote:
>> It's about 10% faster for me. I'm surprised the planner can't figure
>> out that this join is redundant.
>
> Because the join isn't redundant? You're making the assumption that for
> every score.game_id there is exactly one game.id that
On Thu, 9 Apr 2009, Glenn Maynard wrote:
On Thu, Apr 9, 2009 at 7:29 AM, Heikki Linnakangas wrote:
SELECT s.* FROM score s, game g
WHERE s.game_id = g.id AND
s.id IN (
SELECT s2.id FROM score s2 WHERE s2.game_id=g.id ORDER BY s2.score
DESC LIMIT 1
);
You don't really need the join with ga
Glenn Maynard writes:
> http://www.postgresql.org/docs/8.3/static/xfunc-sql.html says this is
> deprecated (though no deprecation warning is being generated):
>> Currently, functions returning sets can also be called in the select list of
>> a query. For each row that the query generates by itse
On Thu, Apr 9, 2009 at 7:29 AM, Heikki Linnakangas
wrote:
> CREATE FUNCTION topnscores(game_id int , n int) RETURNS SETOF score LANGUAGE
> SQL AS $$
> SELECT * FROM score s WHERE s.game_id = $1 ORDER BY score DESC LIMIT $2
> $$;
>
> SELECT (sub.ts).id, (sub.ts).score, (sub.ts).game_id
> FROM (SELE
On Thu, 9 Apr 2009, tiv00 wrote:
create or replace function explode_array(in_array anyarray) returns setof
anyelement as
$$
select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;
Note that you can make this function a bit more general by using
array_lo
2009/4/9 Віталій Тимчишин :
> create or replace function explode_array(in_array anyarray) returns setof
> anyelement as
> $$
> select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
> $$
> language sql immutable;
I tried using an ARRAY like this, but didn't quite figure out the
explod
On Thu, Apr 9, 2009 at 7:29 AM, Heikki Linnakangas
wrote:
>> SELECT s.* FROM score s, game g
>> WHERE s.game_id = g.id AND
>> s.id IN (
>> SELECT s2.id FROM score s2 WHERE s2.game_id=g.id ORDER BY s2.score
>> DESC LIMIT 1
>> );
>
> You don't really need the join with game here, simplifying th
Glenn Maynard wrote:
This rewrite allows getting the top N scores. Unfortunately, this one
takes 950ms for the same data. With 100 scores, it takes 14800ms.
SELECT s.* FROM score s, game g
WHERE s.game_id = g.id AND
s.id IN (
SELECT s2.id FROM score s2 WHERE s2.game_id=g.id ORDER BY s
OK, got to my postgres. Here you are:
create or replace function explode_array(in_array anyarray) returns setof
anyelement as
$$
select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;
SELECT s.* FROM score s
WHERE s.id IN (
select
-- Get the high scorin
(I didn't notice that I ended up with "score.score" in this test case. Oops.)
2009/4/8 Віталій Тимчишин :
> How about
>
> select s1.*
> from score s1 join score s2 on s1.game_id=s2.game_id and s2.score >=
> s1.score
> group by s1.*
> having count(s2.*) <= N
I can see what this is doing, but I'm
2009/4/9 Glenn Maynard
> (This is related to an earlier post on -sql.)
>
> I'm querying for the N high scores for each game, with two tables:
> scores and games.
>
> CREATE TABLE game (id SERIAL NOT NULL PRIMARY KEY);
> CREATE TABLE score (id SERIAL NOT NULL PRIMARY KEY, score REAL,
> game_id INT
(This is related to an earlier post on -sql.)
I'm querying for the N high scores for each game, with two tables:
scores and games.
CREATE TABLE game (id SERIAL NOT NULL PRIMARY KEY);
CREATE TABLE score (id SERIAL NOT NULL PRIMARY KEY, score REAL,
game_id INTEGER REFERENCES game (id));
-- test dat
13 matches
Mail list logo
