Re: [HACKERS] Re: Question regarding new windowing functions in 8.4devel

On Fri, Jan 16, 2009 at 12:07 PM, David Fetter <david@fetter.org> wrote:
>>
>> 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;
>

maybe the rank should go in a having clause? i'm not familiar about
window functions yet... just guessing...

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

the WINDOW specification goes after the WHERE clause not before



--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157