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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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