Numbering rows

May be this function can help :

http://www.postgresql.org/docs/8.3/static/functions-srf.html

Ries
On Oct 15, 2008, at 1:44 PM, Mark Morgan Lloyd wrote:

On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk <pg@rvt.dds.nl> wrote:

May be this function can help :

http://www.postgresql.org/docs/8.3/static/functions-srf.html

Using generate series won't number the rows that way that you would
want. You basically will end up with a cross join between the
generated series and the requested set. There are three ways that I
know of to get a row number:

1) IIRC use a pl-pgsql function that returns an incremented number
2) use SQL by joining using the operator ">=" and Group by aggregate count(*)
3) 8.4 has sum new analytic functions that will do this nicely.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Wed, Oct 15, 2008 at 1:23 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:

On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk <pg@rvt.dds.nl> wrote:

May be this function can help :

http://www.postgresql.org/docs/8.3/static/functions-srf.html

Using generate series won't number the rows that way that you would
want. You basically will end up with a cross join between the
generated series and the requested set. There are three ways that I
know of to get a row number:

Can't you put the query into a subselect with an offset 0 and join to
that to get the generate_series to work correctly?

On Wed, Oct 15, 2008 at 12:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Can't you put the query into a subselect with an offset 0 and join to
that to get the generate_series to work correctly?

I've never heard of doing it that way, but I'm very interestes in
seeing how it is done. This is what i've tried so far, but am still
getting the cross join:

postgres=#
select * from generate_series(1,3)
CROSS JOIN ( values('a'),('b'),('c') ) as myvals( letter )
OFFSET 0;

generate_series | letter
-----------------+--------
1 | a
2 | a
3 | a
1 | b
2 | b
3 | b
1 | c
2 | c
3 | c
(9 rows)

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Thanks everybody- I'm watching with a lot of interest. I was worried
that I was asking something stupid with an obvious answer...

ries van Twisk wrote:

May be this function can help :

http://www.postgresql.org/docs/8.3/static/functions-srf.html

Thanks, that's already turning out to be useful for something else I was
working on today:

select percent,
to_char(nedcar_tonnes_001(percent), '99990.99') AS nedcar
from generate_series(0,110,10) as percent;

The one thing I'd say about generate_series() is that the description
suggests that one has to put an explicit count() as the second parameter
if using it to number rows, i.e. it doesn't have an "as many as
necessary" option.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

Mark Morgan Lloyd wrote:

Is there an easy way to assign a sequential number, possibly based on an
arbitrary minimum (typically 0 or 1) to each row of an ordered result
set, or do I have to work with explicit sequences?

I need to do quite a lot of maths on successive rows, extracting numeric
and timestamp differences hence rates of change. I've typically been
doing it manually or in a spreadsheet but there has to be a better way
e.g. by a join on offset row numbers.

PERL can remember variables in your session. Here's a function I wrote
that sets a "global" variable in PL/PERL:

----------
CREATE OR REPLACE FUNCTION "public"."global_var_set"
(in_key varchar, in_value bigint)
RETURNS bigint AS
$body$
my ($key, $value) = @_;
$_SHARED{$key} = $value;
return $value;
$body$
LANGUAGE 'plperl' VOLATILE RETURNS NULL ON NULL INPUT;
----------

Then, later you can read that global variable with another function like
this:

----------
CREATE OR REPLACE FUNCTION "public"."global_var_get"
(in_key varchar)
RETURNS bigint AS
$body$
my ($key) = @_;
return $_SHARED{$key} ? $_SHARED{$key} : 0;
$body$
LANGUAGE 'plperl' VOLATILE RETURNS NULL ON NULL INPUT;
----------

Perhaps you can use PL/PERL and a function like these to modify "global"
variables that you can increment as you do your select. Something like:

SELECT global_var_set(0);

SELECT global_var_inc() AS row_counter, *
FROM datatable
ORDER BY whatever;

Just an idea.

-- Dante

----------
D. Dante Lorenso
dante@lorenso.com

D. Dante Lorenso wrote:

PERL can remember variables in your session. Here's a function I wrote
that sets a "global" variable in PL/PERL:

Perl can do anything- that's cheating :-)

Actually, I use Perl heavily but the advantage of being able to do the
sort of analysis being discussed in a single query is that the query can
be easily shipped with the results as a description of the method.
Having to set up ad-hoc extra functions (in addition to those that are
normally in the workflow) can be problematic, particularly if a
recipient of the query only has restricted access.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

am Wed, dem 15.10.2008, um 12:23:42 -0700 mailte Richard Broersma folgendes:

On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk <pg@rvt.dds.nl> wrote:

May be this function can help :

http://www.postgresql.org/docs/8.3/static/functions-srf.html

Using generate series won't number the rows that way that you would
want. You basically will end up with a cross join between the
generated series and the requested set. There are three ways that I
know of to get a row number:

1) IIRC use a pl-pgsql function that returns an incremented number
2) use SQL by joining using the operator ">=" and Group by aggregate count(*)
3) 8.4 has sum new analytic functions that will do this nicely.

Can you show an example for 8.4?

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

Andreas Kretschmer wrote:

Can you show an example for 8.4?

It's not 100% certain that it will be possible for 8.4, probably though.

select row_number() over (order by employeeid) as nrow,* from employee order
by employeeid

It's important to have both the order bys

There is more information on windowing functions here
http://en.wikipedia.org/wiki/SELECT

David.

David Rowley wrote:

It's not 100% certain that it will be possible for 8.4, probably though.

select row_number() over (order by employeeid) as nrow,* from employee order
by employeeid

That makes sense, thanks. So extracting rate-of-change etc. would be a
join on two subselects followed by a bit of maths, that's likely to be a
big incentive for an upgrade when it comes out.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

On Wed, Oct 15, 2008 at 10:21 PM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:

Can you show an example for 8.4?

I looked for ROW_NUMBER in the developer docs. I could only find it
under the KEY WORDS list. I guess they haven't put in a good example
yet.

I saw this recently demonstrated at PgWest by Dave Fetter. He
illustrated several example of how to use analytic functions such as
these.

http://fetter.org/Window_Functions_WEST_2008.pdf
http://fetter.org/Trees_and_More_WEST_2008.pdf

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

"Richard Broersma" <richard.broersma@gmail.com> writes:

I looked for ROW_NUMBER in the developer docs. I could only find it
under the KEY WORDS list. I guess they haven't put in a good example
yet.

I saw this recently demonstrated at PgWest by Dave Fetter. He
illustrated several example of how to use analytic functions such as
these.

Fetter was demo'ing uncommitted patches.

regards, tom lane