1

When I do select query on single table then I get output rows ordered by primary key which is perfect, following query:

select * from recondata where importid=131;

but when I'm joining this table to another table output of row is random, ReconStatus contains only statusId (Primary key) and Status and in ReconData, dataID is primary key and there are no other key. I'm using following query:

select dataid,coalesce(clientPortfolioname,portfolioName) as portfolioName,
                            Status, Message, PositionId, PositionIdType,    PositionName, ClientMarketValue,
                            ClientCurrency, ClientMarketValueBase, MarketValueBase, MarketValueLocal,
                            MVDiff, SourceInstrumentType, Weight, WeightByClient, ImportType,AssetType,
                            AssetName,ExtraInfo
                            from 
                            ReconData rd inner join ReconStatus rs on rd.StatusId = rs.statusid
                            where
                            ImportId=131;

Output is also not based on primary key of second table. Can someone explain on what basis MS SQL is ordering output rows ?

techExplorer
  • 261
  • 1
  • 4

1 Answers1

0

You cannot predict the order of a query. Even though when the query is only reading one table. This has to dowith several factors like vacuuming or used indexes. Always think in sets with databases.

user1363989
  • 181
  • 4