Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B7C794753A1 for ; Thu, 21 Nov 2002 01:43:09 -0500 (EST) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 7091C474E53 for ; Thu, 21 Nov 2002 01:43:01 -0500 (EST) Received: (qmail 32079 invoked from network); 21 Nov 2002 06:32:11 -0000 Received: from unknown (HELO system67.trade-india-local.com) (192.168.0.67) by infocom-236-129-del.trade-india.com with SMTP; 21 Nov 2002 06:32:11 -0000 Content-Type: text/plain; charset="us-ascii" From: "Rajesh Kumar Mallah." Organization: Infocom Network Limited. To: pgsql-sql@postgresql.org Subject: why the difference? Date: Thu, 21 Nov 2002 12:17:09 +0530 User-Agent: KMail/1.4.1 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200211211217.09970.mallah@trade-india.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/321 X-Sequence-Number: 10463 Hi folk, i am finding something mysterious in SQL can anyone explain? consider the SQL: tradein_clients=3D# select distinct on (amount,co_name,city) category_id,= amount,co_name,city from eyp_listing=20 where keywordidx ## 'vegetable' and category_id=3D781 ; category_id | amount | co_name | city -------------+--------+---------------------------+------------ 781 | 0 | ANURADHA EXPORTS | CHENNAI 781 | 0 | R.K.INTERNATIONAL | CHENNAI 781 | 0 | SAI IMPEX | MUMBAI 781 | 0 | TRIMA ENTERPRISES | CHENNAI 781 | 0 | UNIQUE DEHYDRATES LIMITED | AHMEDABAD 781 | 5000 | RSV EXPORT | COIMBATORE (6 rows) lets remove the contraint "category_id=3D781" and store the output in a tab= le "t_a". tradein_clients=3D# CREATE TABLE t_a AS select distinct on (amount,co_name= ,city) category_id,amount,co_name,city from=20 eyp_listing where keywordidx ## 'vegetable' ; then when i select from t_a with category_id=3D781 i have less secords tradein_clients=3D# SELECT * from t_a where category_id=3D781; category_id | amount | co_name | city -------------+--------+---------------------------+------------ 781 | 0 | R.K.INTERNATIONAL | CHENNAI 781 | 0 | SAI IMPEX | MUMBAI 781 | 0 | UNIQUE DEHYDRATES LIMITED | AHMEDABAD 781 | 5000 | RSV EXPORT | COIMBATORE (4 rows) Can anyone please explain the difference? Regds Mallah. --=20 Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.