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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
