>
>
>One SELECT is
>
>SELECT MOC_ITEMS.MOCITEMID, PARTIES.NAME

--8<--

>MOC_SUBCATEGORIES.MOCSUBCATEGORYID
>ORDER BY PARTIES.NAME ASC
>
>Another one is
>
>SELECT
> MOC_ITEMS.MOCITEMID,

--8<--

> INNER JOIN MOC_SUBCATEGORIES ON MOC_ITEMS.MOCSUBCATEGORYID =
>MOC_SUBCATEGORIES.MOCSUBCATEGORYID
> ORDER BY PARTIES.NAME ASC
>
>When i am running both in IBExpert, it shows slightly different
>resultsets. The order of ID values differs...The same goes when
>invoke these SQL from my application.
>
>Can anyone explain, why?

I bet they have different plans as well.

Here's the deal: if you do NOT specify an ORDER BY for specific
columns, the order of the resultset for those columns is "undefined",
meaning that the DBMS is free to return the rows in any order specified.

Given that you only have an ORDER BY on column PARTIES.NAME,
would it be true that the resultsets includes parties with the same name?
If so, the corresponding ID-values could be in different orders.


Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com