Thread: 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.
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 >
Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz> writes:
> 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...
Besides which, SELECT DISTINCT ON is order-sensitive. If you don't use
an ORDER BY with it, you are going to get rather unpredictable results.
See the example on the SELECT reference page.
regards, tom lane