Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aT0ng-0001wh-T4 for pgsql-sql@arkaria.postgresql.org; Tue, 09 Feb 2016 05:25:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aT0nf-0005wR-N7 for pgsql-sql@arkaria.postgresql.org; Tue, 09 Feb 2016 05:25:19 +0000 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aT0mf-0004Qq-PF for pgsql-sql@postgresql.org; Tue, 09 Feb 2016 05:24:17 +0000 Received: from mailuogwhop.emc.com ([168.159.213.141]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aT0kX-00060g-OY for pgsql-sql@postgresql.org; Tue, 09 Feb 2016 05:22:09 +0000 Received: from maildlpprd06.lss.emc.com (maildlpprd06.lss.emc.com [10.253.24.38]) by mailuogwprd04.lss.emc.com (Sentrion-MTA-4.3.1/Sentrion-MTA-4.3.0) with ESMTP id u195M1H1023478 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NO); Tue, 9 Feb 2016 00:22:02 -0500 X-DKIM: OpenDKIM Filter v2.4.3 mailuogwprd04.lss.emc.com u195M1H1023478 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=emc.com; s=jan2013; t=1454995322; bh=bqgvVkvU4WUu7csP2CPeklpDRs0=; h=From:To:CC:Subject:Date:Message-ID:References:In-Reply-To: Content-Type:MIME-Version; b=l1q5b/bizvhGQYhkzGoz8ywbXFSbpFfxJ50RWezDIqXv1Hci+mh0nKPRn7P7TtXEP lEoCPmLhBZLC2LT7bc4KU8d1P1m3xJ9Th7t/pQl4+r0V/VotAxS2yrkLv03UuqnM1q y/XYCr7Q/QCyYt2BL+mbsefI4UfFlMZqFAY8zYdM= X-DKIM: OpenDKIM Filter v2.4.3 mailuogwprd04.lss.emc.com u195M1H1023478 Received: from mailusrhubprd01.lss.emc.com (mailusrhubprd01.lss.emc.com [10.253.24.19]) by maildlpprd06.lss.emc.com (RSA Interceptor); Tue, 9 Feb 2016 00:21:12 -0500 Received: from MXHUB221.corp.emc.com (MXHUB221.corp.emc.com [10.253.68.91]) by mailusrhubprd01.lss.emc.com (Sentrion-MTA-4.3.1/Sentrion-MTA-4.3.0) with ESMTP id u195Ljvd030044 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=FAIL); Tue, 9 Feb 2016 00:21:46 -0500 Received: from MX105CL01.corp.emc.com ([fe80::e020:347b:74e3:4176]) by MXHUB221.corp.emc.com ([10.253.68.91]) with mapi id 14.03.0266.001; Tue, 9 Feb 2016 00:21:45 -0500 From: "Venkatesan, Sekhar" To: Tom Lane CC: "pgsql-sql@postgresql.org" Subject: Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Thread-Topic: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Thread-Index: AdFi8MH6WiSn/w2ETze/qHqqolOx7gAL+aqAAAo3P3A= Date: Tue, 9 Feb 2016 05:21:45 +0000 Message-ID: References: <21082.1454994009@sss.pgh.pa.us> In-Reply-To: <21082.1454994009@sss.pgh.pa.us> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.30.85.98] Content-Type: multipart/alternative; boundary="_000_F84DE43FDACD4C45AA84E2DA016FAE2F1C65BEC8MX105CL01corpem_" MIME-Version: 1.0 X-Sentrion-Hostname: mailusrhubprd01.lss.emc.com X-RSA-Classifications: public X-Pg-Spam-Score: -4.6 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_000_F84DE43FDACD4C45AA84E2DA016FAE2F1C65BEC8MX105CL01corpem_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Tom, You can disregard the "TOP 10" modifier. That was added by me to bring down= the huge number of results being returned. Even without the TOP modifier, SQL server is returning rows in sorted order= (sorting columns based on the r_object_id (1st) column I think) but Postgr= eSQL doesn't. Is this anything to do with indexes? So from what I understand, you say in postgres, if the sort order is not sp= ecified, postgres returns results in any order. Am I right? -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, February 09, 2016 10:30 AM To: Venkatesan, Sekhar Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and sort o= rder. "Venkatesan, Sekhar" > writes: > I am seeing this behavior change in postgreSQL DB when compared to SQL Se= rver DB when JOIN is performed. The sort order is not retained when JOIN is= performed in PostgreSQL DB. What sort order? You did not specify any ORDER BY clause, so the DBMS is e= ntitled to return rows in any order it feels like. I do not know anything about this "top 10" modifier you've got in the SQL S= erver version, but I suspect it's implying a sort order. In Postgres, if y= ou want a specific row ordering, you need to say ORDER BY. regards, tom lane --_000_F84DE43FDACD4C45AA84E2DA016FAE2F1C65BEC8MX105CL01corpem_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi Tom,

 

You can disregard the "TOP 10" modifier= . That was added by me to bring down the huge number of results being retur= ned.

Even without the TOP modifier, SQL server is retu= rning rows in sorted order (sorting columns based on the r_object_id (1st) column I think) but PostgreSQL doesn’t.

Is this anything to do with indexes?

So from what I understand, you say in postgres, i= f the sort order is not specified, postgres returns results in any order. A= m I right?

 

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, February 09, 2016 10:30 AM
To: Venkatesan, Sekhar
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and sort o= rder.

 

"Venkatesan, Sekhar" <sekhar.venkatesan@emc.com> writes:

> I am seeing this behavior change in postgreS= QL DB when compared to SQL Server DB when JOIN is performed. The sort order= is not retained when JOIN is performed in PostgreSQL DB.

 

What sort order?  You did not specify any OR= DER BY clause, so the DBMS is entitled to return rows in any order it feels= like.

 

I do not know anything about this "top 10&qu= ot; modifier you've got in the SQL Server version, but I suspect it's imply= ing a sort order.  In Postgres, if you want a specific row ordering, y= ou need to say ORDER BY.

 

        &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;  regards, tom lane

--_000_F84DE43FDACD4C45AA84E2DA016FAE2F1C65BEC8MX105CL01corpem_--