Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6ALQoa20210 for ; Tue, 10 Jul 2001 17:26:50 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f64Fkaa38398 for ; Wed, 4 Jul 2001 11:46:36 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f64FPA502232 for pgsql-sql@postgresql.org; Wed, 4 Jul 2001 11:25:10 -0400 (EDT) (envelope-from news) From: joe.celko@trilogy.com (--CELKO--) X-Newsgroups: comp.databases.postgresql.sql Subject: Re: need oracle ROWNUM equivalent Date: 2 Jul 2001 13:56:11 -0700 Organization: http://groups.google.com/ Lines: 75 Message-ID: <7e67a7b3.0107021256.7d961f9@posting.google.com> References: <9hjltr$258l$1@news.tht.net> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-support@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200107/127 X-Sequence-Number: 3915 >> 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--