Received: from nf.enveng.titech.ac.jp (nf.enveng.titech.ac.jp [131.112.148.3]) by hub.org (8.9.3/8.9.3) with ESMTP id CAA15057 for ; Wed, 15 Dec 1999 02:36:59 -0500 (EST) (envelope-from masao@nf.enveng.titech.ac.jp) Received: from localhost (neon.enveng.titech.ac.jp [131.112.148.211]) by nf.enveng.titech.ac.jp (8.8.7/8.8.7) with ESMTP id QAA01524 for ; Wed, 15 Dec 1999 16:31:40 +0900 (JST) (envelope-from masao@nf.enveng.titech.ac.jp) To: pgsql-sql@hub.org Subject: Re: [SQL] numbered table? In-Reply-To: <19991210192346T.masao@nf.enveng.titech.ac.jp> References: <19991210153410K.masao@nf.enveng.titech.ac.jp> <19991210192346T.masao@nf.enveng.titech.ac.jp> X-Mailer: Mew version 1.94.1 on Emacs 20.4 / Mule 4.0 (HANANOEN) Mime-Version: 1.0 Content-Type: Text/Plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-Id: <19991215164328A.masao@nf.enveng.titech.ac.jp> Date: Wed, 15 Dec 1999 16:43:28 +0900 From: UEBAYASHI Masao X-Dispatcher: imput version 990905(IM130) Lines: 26 > I've found a topic near my question, ranking, in ``SQL FOR > SMARTIES''. Thanks. At last, I dumped this ranking method. Celko's suggestion was: SELECT T1.attrib0, T1.attrib1, (SELECT COUNT(DISTINCT attrib1) FROM Table AS T2 WHERE (T2.attrib1 >= T1.attrib1) AND (T2.attrib0 = T1.attrib0)) AS rank FROM Table AS T1 WHERE rank <= :n; or SELECT T1.attrib0, T1.attrib1, (SELECT COUNT(attrib1) FROM Table AS T2 WHERE (T2.attrib1 >= T1.attrib1) AND (T2.attrib0 = T2.attrib0)) AS rank FROM Table AS T1 WHERE rank <= :n; Unfortunately, neither don't run in PostgreSQL. Masao