Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.

Sekhar,

You will have to specify a sort order with "order by <field>" clause before
the limit clause. It's the only way I know the order to be guaranteed to
remain the same. Hope this helps.

Stuart
On Feb 9, 2016 08:30, "Venkatesan, Sekhar" <sekhar(dot)venkatesan(at)emc(dot)com>
wrote:

> Hi folks,
>
>
>
> I am seeing this behavior change in postgreSQL DB when compared to SQL
> Server DB when JOIN is performed. The sort order is not retained when JOIN
> is performed in PostgreSQL DB.
>
> Is it expected? Is there a solution available to retain the sort order
> during JOIN? We have applications that expects the same sort order during
> JOIN and we want to support our application on PostgreSQL DB.
>
> DO we need to indicate to the PostgreSQL DB optimizer to not change the
> sort order? If so, how to do it and what are it’s implications.
>
>
>
> From the below example, you can see that the results are not in sorted
> order in PostgreSQL when compared to SQL Server DB.
>
>
>
> *SQLServer:*
>
>
>
> SELECT top 10 KH_.r_object_id, KH_.object_name FROM dbo.dm_location_s
> AS ZS_ INNER JOIN
>
> dbo.dm_sysobject_s AS KH_ ON ZS_.r_object_id = KH_.r_object_id
>
>
> 3a00d5128000013f storage_01
>
> 3a00d51280000140 common
>
> 3a00d51280000141 events
>
> 3a00d51280000142 log
>
> 3a00d51280000143 config
>
> 3a00d51280000144 dm_dba
>
> 3a00d51280000145 auth_plugin
>
> 3a00d51280000146 ldapcertdb_loc
>
> 3a00d51280000147 temp
>
> 3a00d51280000148 dm_ca_store_fetch_location
>
>
>
> *PostgreSQL:*
>
>
>
> dm_repo6_docbase=> SELECT KH_.r_object_id, KH_.object_name FROM
> dm_location_s AS ZS_ INNER JOIN dm_sysobject_s AS KH_ ON ZS_.r_object_id =
> KH_.r_object_id limit 10;
>
>
>
> r_object_id | object_name
>
> ------------------+---------------------------
>
> 3a0003e98000a597 | TDfFXMigrateRMOPDQ71486_1
>
> 3a0003e980007679 | 738296_2
>
> 3a0003e980000142 | log
>
> 3a0003e980000143 | config
>
> 3a0003e980000140 | common
>
> 3a0003e98000013f | storage_01
>
> 3a0003e980000141 | events
>
> 3a0003e980000144 | dm_dba
>
> 3a0003e980000145 | auth_plugin
>
> 3a0003e980000146 | ldapcertdb_loc
>
> (10 rows)
>
>
>
> Thanks,
>
> Sekhar
>