Re: Question regarding new windowing functions in 8.4devel

On Thu, Jan 15, 2009 at 03:06:47PM +0100, A. Kretschmer wrote:
> 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;

I tried this:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WINDOW w AS (partition by typ order by ts desc)
WHERE
foo_rank < 4;

ERROR: syntax error at or near "WHERE"
LINE 8: WHERE
^
Possibly the above is not a bug, but I'm pretty sure this is:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WINDOW w AS (partition by typ order by ts desc)
WHERE
typ < 4;

ERROR: syntax error at or near "WHERE"
LINE 8: WHERE
^

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate