From: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
To: Rajesh Kumar Mallah. <mallah@trade-india.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: why the difference?
Date: Thu, 21 Nov 2002 09:42:33 +0100 (CET)
Message-ID: <Pine.LNX.4.44.0211210936330.11841-100000@server> (raw)
In-Reply-To: <200211211217.09970.mallah@trade-india.com>
hi,
the where clause is evaluated before the distinct clause, so your queries
aren't equivalent because you switched the order by splitting the query
into two queries... so to obtain same results do create table as select
... where category_id=781 and then select distinct on () ...
hth,
kuba
> tradein_clients=# select distinct on (amount,co_name,city) category_id,amount,co_name,city from eyp_listing
> where keywordidx ## 'vegetable' and category_id=781 ;
>
> 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=781" and store the output in a table "t_a".
>
> tradein_clients=# CREATE TABLE t_a AS select distinct on (amount,co_name,city) category_id,amount,co_name,city from
> eyp_listing where keywordidx ## 'vegetable' ;
>
> then when i select from t_a with category_id=781 i have less secords
>
> tradein_clients=# SELECT * from t_a where category_id=781;
> 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.
>
>
>
>
>
>
>
>
> --
> 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.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
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: jouh8664@ss1000.ms.mff.cuni.cz, mallah@trade-india.com
Subject: Re: why the difference?
In-Reply-To: <Pine.LNX.4.44.0211210936330.11841-100000@server>
* 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