Thread: numbering rows in a view

numbering rows in a view

From
richard terry
Date:
I need a unique numbering for a view row and I can recall somewhere that
postgres was getting a new function which would enable this to happen, I've
searched the docs but I mustn't be putting in the right key words, so I
wondered if someone could enlighten me.

Thanks

richard

Re: numbering rows in a view

From
Thomas Kellerer
Date:
richard terry wrote on 30.08.2009 13:26:
> I need a unique numbering for a view row and I can recall somewhere that
> postgres was getting a new function which would enable this to happen, I've
> searched the docs but I mustn't be putting in the right key words, so I
> wondered if someone could enlighten me.

You are probably looking for row_number() which was introduced in 8.4

http://www.postgresql.org/docs/current/static/functions-window.html

Something like:

SELECT <your_column_list_here>, row_number() over ()
FROM ...

Thomas

Re: numbering rows in a view

From
richard terry
Date:
On Sunday 30 August 2009 21:53:15 Thomas Kellerer wrote:
> richard terry wrote on 30.08.2009 13:26:
> > I need a unique numbering for a view row and I can recall somewhere that
> > postgres was getting a new function which would enable this to happen,
> > I've searched the docs but I mustn't be putting in the right key words,
> > so I wondered if someone could enlighten me.
>
> You are probably looking for row_number() which was introduced in 8.4
>
> http://www.postgresql.org/docs/current/static/functions-window.html
>
> Something like:
>
> SELECT <your_column_list_here>, row_number() over ()
> FROM ...
>
> Thomas
thanks, I'll check it out.