pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhy the difference?
3+ messages / 3 participants
[nested] [flat]
* why the difference?
@ 2002-11-21 06:47 Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-11-21 08:42 ` Re: why the difference? Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
0 siblings, 1 reply; 3+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-11-21 06:47 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: why the difference?
2002-11-21 06:47 why the difference? Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2002-11-21 08:42 ` Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-11-21 14:47 ` Re: why the difference? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Jakub Ouhrabka @ 2002-11-21 08:42 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: why the difference?
2002-11-21 06:47 why the difference? Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-11-21 08:42 ` Re: why the difference? Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
@ 2002-11-21 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2002-11-21 14:47 UTC (permalink / raw)
To: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>; +Cc: Rajesh Kumar Mallah. <mallah@trade-india.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-11-21 14:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-21 06:47 why the difference? Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-11-21 08:42 ` Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-11-21 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
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