Question regarding new windowing functions in 8.4devel

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