Re: why the difference?

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(at)postgresql(dot)org
>