Re: [GENERAL] Numbering rows
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows
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] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows
On Wed, Oct 15, 2008 at 10:21 PM, A. Kretschmer [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows
Richard Broersma [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Numbering rows
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. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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: 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. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows
On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows
On Wed, Oct 15, 2008 at 1:23 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk [EMAIL PROTECTED] 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows
On Wed, Oct 15, 2008 at 12:32 PM, Scott Marlowe [EMAIL PROTECTED] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows
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), '0.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] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows
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
[EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows
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] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows
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 [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows by date
Volkan,
CREATE SEQUENCE document_docorder_seq START 1;
UPDATE document
SET docorder = T.docorder
FROM (SELECT nextval('document_docorder_seq') AS docorder, docdate
FROM document
ORDER BY docdate) AS T
WHERE document.docdate = T.docdate;
DROP SEQUENCE document_docorder_seq;
thank you. Excellent.
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows by date
In article [EMAIL PROTECTED], Andrus [EMAIL PROTECTED] writes: I have table create Document ( docdate date, docorder integer ) I need update docorder column with numbers 1,2 in docdate date order Something like i = 1; UPDATE Document SET docorder = i++ ORDER BY docdate; How to do this is PostgreSQL 8.2 ? I don't think you can avoid a temp table: CREATE TEMP TABLE tmp ( docdate date, docorder serial ); INSERT INTO tmp (docdate) SELECT docdate FROM documents ORDER BY docdate; UPDATE documents d SET docorder = t.docorder FROM tmp t WHERE d.docdate = t.docdate; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows by date
On Wed, 2 Apr 2008, Andrus [EMAIL PROTECTED] writes:
create Document ( docdate date, docorder integer )
I need update docorder column with numbers 1,2 in docdate date order
Something like
i = 1;
UPDATE Document SET docorder = i++
ORDER BY docdate;
CREATE SEQUENCE document_docorder_seq START 1;
UPDATE document
SET docorder = T.docorder
FROM (SELECT nextval('document_docorder_seq') AS docorder, docdate
FROM document
ORDER BY docdate) AS T
WHERE document.docdate = T.docdate;
DROP SEQUENCE document_docorder_seq;
Regards.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Numbering rows by date
I have table create Document ( docdate date, docorder integer ) I need update docorder column with numbers 1,2 in docdate date order Something like i = 1; UPDATE Document SET docorder = i++ ORDER BY docdate; How to do this is PostgreSQL 8.2 ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Numbering rows by date
Andrus wrote:
I have table
create Document ( docdate date, docorder integer )
I need update docorder column with numbers 1,2 in docdate date order
Something like
i = 1;
UPDATE Document SET docorder = i++
ORDER BY docdate;
How to do this is PostgreSQL 8.2 ?
ALTER TABLE DROP COLUMN docorder;
SELECT docdate FROM document ORDER BY docdate ASC;
Or, if you really need the numbering and can't do it in application code
... my first thought was to do this:
CREATE TEMP SEQUENCE seq_doc_number;
SELECT docdate, nextval('seq_doc_number') FROM document
ORDER BY docdate ASC;
But the ordering will occur afterwards so the sequence will be out of
order. I think you'd need a subquery, then:
CREATE TEMP SEQUENCE seq_doc_number;
SELECT docdate, nextval('seq_doc_number') AS docorder
FROM (SELECT docdate FROM document ORDER BY docdate ASC) dummy_alias;
b
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
