Thread: why the difference?

why the difference?

From
"Rajesh Kumar Mallah."
Date:
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.




Re: why the difference?

From
Jakub Ouhrabka
Date:
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
>



Re: why the difference?

From
Tom Lane
Date:
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