pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: joe.celko@trilogy.com
To: pgsql-sql@postgresql.org
Subject: Re: need oracle ROWNUM equivalent
Date: 2 Jul 2001 13:56:11 -0700
Message-ID: <7e67a7b3.0107021256.7d961f9@posting.google.com> (raw)
References: <9hjltr$258l$1@news.tht.net>

>> I need Oracle ROWNUM equivalent.  For doing following operation
 
  -----------   ------------      -------------
  VIEW Y.XL      VIEW Y.XR        VIEW Y_XL_XR
  1           +   5          =         1    5
  6               10                   6    10
  12              20                   12   20
 
 With Oracle I can use ROWNUM as key to join the two views; Is there
any other ways to do that? <<


Never use a row number.  It is not part of a logical model, it is not
relational, it is not portable.  Please post DDL, so that people do
not have to guess what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema.  Here is what I am
assuming:

I have two tables. Rows in Table A have a column x which needs to be
set to the primary key of a row from Table B.   The first row of table
A should point to the first row of table B, the second row of Table A
should point to the second row of table B, etc. There are many more
rows in Table A than in Table B and I would like to match column x to
Table B in a round-robin fashion.

CREATE TABLE A
(keycol INTEGER NOT NULL PRIMARY KEY,
  x   INTEGER)

INSERT INTO A VALUES (1, NULL);
INSERT INTO A VALUES (2, NULL);
INSERT INTO A VALUES (3, NULL);
INSERT INTO A VALUES (7, NULL);
INSERT INTO A VALUES (10, NULL);

CREATE TABLE B
(keycol INTEGER NOT NULL PRIMARY KEY);

INSERT INTO B VALUES (10);
INSERT INTO B VALUES (24);
INSERT INTO B VALUES (32);

Warning: while this is portable, it is not going to be fast.

UPDATE A
   SET x = (SELECT keycol
              FROM B
             WHERE ((SELECT COUNT(A1.keycol)
                      FROM A AS A1
                     WHERE A1.keycol < A.keycol)
                    % (SELECT COUNT(*) FROM B)) +1 
                   = (SELECT COUNT(B1.keycol) 
                         FROM B AS B1
                        WHERE B1.keycol <= B.keycol)); 

SELECT * FROM A;

(5 row(s) affected)

keycol      x           
----------- ----------- 
1           10
2           24
3           32
7           10
10          24

Basically, I use the keys to determining the size of the subset of key
columns within a boundary established by the current row in each table
via those correlated subquery expressions.

The rest is MOD arithmetic; add one to get rid of the 0 in the modulus
cycle, use  < and <= in the comparisons to adjust the subset counts.

--CELKO--



Message-ID: <7e67a7b3.0107021256.7d961f9@posting.google.com>
Permalink:  ../7e67a7b3.0107021256.7d961f9@posting.google.com/
Also on:    postgresql.org/message-id/7e67a7b3.0107021256.7d961f9@posting.google.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: joe.celko@trilogy.com
  Subject: Re: need oracle ROWNUM equivalent
  In-Reply-To: <7e67a7b3.0107021256.7d961f9@posting.google.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox