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
orderby 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