1

This question already has an answer here:

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 ?

marked as duplicate by Mikael Eriksson, ypercubeᵀᴹ, Paul White, Philᵀᴹ, Mark Storey-Smith May 9 '14 at 11:07

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    Please don't assume there is a default sorting without using order by. See Hugo's blog for counterexample. – vonPryz May 9 '14 at 8:55
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.