Question regarding new windowing functions in 8.4devel

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Question regarding new windowing functions in 8.4devel
Date: 2009-01-15 14:06:47
Message-ID: 20090115140647.GC22144@a-kretschmer.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hi,

first, many thanks to all for the great work, i'm waiting for 8.4.

I have played with the new possibilities:

test=# select typ, ts, rank() over (partition by typ order by ts desc ) from foo;
typ | ts | rank
-----+-------------------------------+------
1 | 2009-01-15 13:03:57.667631+01 | 1
1 | 2009-01-15 13:03:56.554659+01 | 2
1 | 2009-01-15 13:03:55.694803+01 | 3
1 | 2009-01-15 13:03:54.816871+01 | 4
1 | 2009-01-15 13:03:53.521454+01 | 5
2 | 2009-01-15 13:04:02.223655+01 | 1
2 | 2009-01-15 13:04:01.30692+01 | 2
2 | 2009-01-15 13:04:00.05923+01 | 3
3 | 2009-01-15 13:04:14.27154+01 | 1
3 | 2009-01-15 13:04:05.395805+01 | 2
3 | 2009-01-15 13:04:04.365645+01 | 3
4 | 2009-01-15 13:04:11.54897+01 | 1
4 | 2009-01-15 13:04:10.778115+01 | 2
4 | 2009-01-15 13:04:10.013001+01 | 3
4 | 2009-01-15 13:04:09.324396+01 | 4
4 | 2009-01-15 13:04:08.523507+01 | 5
4 | 2009-01-15 13:04:07.375874+01 | 6
(17 rows)

Okay, fine.

Now i want only 3 records for every typ:

test=# select typ, ts, rank() over (partition by typ order by ts desc ) from foo where rank <= 3;
ERROR: column "rank" does not exist
LINE 1: ...rtition by typ order by ts desc ) from foo where rank <= 3;

Okay, next try:

test=# select typ, ts, rank() over (partition by typ order by ts desc ) from foo where rank() over (partition by typ order by ts desc ) <= 3;
ERROR: window functions not allowed in WHERE clause
LINE 1: ...rtition by typ order by ts desc ) from foo where rank() ove...

Ouch.

I found a way with a subselect:

test=# select * from (select typ, ts, rank() over (partition by typ order by ts desc ) from foo) bla where rank <= 3;
typ | ts | rank
-----+-------------------------------+------
1 | 2009-01-15 13:03:57.667631+01 | 1
1 | 2009-01-15 13:03:56.554659+01 | 2
1 | 2009-01-15 13:03:55.694803+01 | 3
2 | 2009-01-15 13:04:02.223655+01 | 1
2 | 2009-01-15 13:04:01.30692+01 | 2
2 | 2009-01-15 13:04:00.05923+01 | 3
3 | 2009-01-15 13:04:14.27154+01 | 1
3 | 2009-01-15 13:04:05.395805+01 | 2
3 | 2009-01-15 13:04:04.365645+01 | 3
4 | 2009-01-15 13:04:11.54897+01 | 1
4 | 2009-01-15 13:04:10.778115+01 | 2
4 | 2009-01-15 13:04:10.013001+01 | 3
(12 rows)

Is there a better way to do that?

(current 8.4devel, today compiled)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kirk Strauser 2009-01-15 15:54:50 Why would I want to use connection pooling middleware?
Previous Message Brendan Jurd 2009-01-15 13:50:54 Re: fire trigger for a row without update?

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2009-01-15 14:15:55 Re: BUG #4566: pg_stop_backup() reports incorrect STOP WAL LOCATION
Previous Message Brendan Jurd 2009-01-15 13:50:54 Re: fire trigger for a row without update?