Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA24366 for ; Wed, 15 Dec 1999 11:30:11 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id LAA18364; Wed, 15 Dec 1999 11:28:15 -0500 (EST) To: UEBAYASHI Masao cc: pgsql-sql@hub.org Subject: Re: [SQL] numbered table? In-reply-to: Your message of Wed, 15 Dec 1999 16:43:28 +0900 <19991215164328A.masao@nf.enveng.titech.ac.jp> Date: Wed, 15 Dec 1999 11:28:15 -0500 Message-ID: <18362.945275295@sss.pgh.pa.us> From: Tom Lane UEBAYASHI Masao writes: > 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. FWIW, this does work in current development sources, with the exception of the final "WHERE rank ..." clause --- our parser doesn't think that AS-names from the SELECT list are valid in WHERE, and after looking at the SQL spec I have to agree with it. So you'd need to repeat the sub-SELECT expression in WHERE :-(. I don't have a clever idea at the moment for rewriting the query to avoid the 6.5.* restrictions you're running into (no COUNT DISTINCT, no sub-SELECTs in target lists). regards, tom lane