why the difference?

Hi folk,

i am finding something mysterious in SQL can anyone explain?

consider the SQL:

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.