CODEWITHSUNDEEP
sqldatabase
Sadiq
Sadiq

Reputation: 838

Difference in resulted order of records for same query

I have this query:

SELECT -- some columns -- 
FROM ABC 
     INNER JOIN DEF ON DEF._VALUE = SUBSTRING(ABC._VALUE, 1, 6) 
     INNER JOIN GHI ON GHI.CODE = DEF.CODE 
     LEFT OUTER JOIN DC ON DC.PAN = ABC.PAN 
     LEFT OUTER JOIN BR ON BR.NAME = DC.NAME 
WHERE ABC.RECEIVED_DATE >= CONVERT(DATETIME,'2014-01-01 00:00:00',120) 
      AND ABC.RECEIVED_DATE <= CONVERT(DATETIME,'2014-01-10 23:59:59',120) 
      AND ('-1' IN ('-1') OR ABC.ID IN ('-1')) 
      AND ('-1' IN ('2012') OR BR.NAME IN ('2012')) 
      AND ABC.PAN LIKE '%' + '%' + '%' 
      AND DC.RL LIKE '%' + '%' + '%' 
      AND (ABC.RC = '1')

Every time it hits the DataBase it gets the SAME NUMBER (say 15) of records but the ORDER of the records IS DIFFERENT every time. Like if, in first attempt the record that was shown as record#-1 is shown record#-6 in second attempt may be. In otherwords, the order gets disturbed (order not consistent every time).

What could be the possible reasons? Because of Indexes or something? I do not no for sure why is it so. Guide me please

Upvotes: 0

Views: 45

Answers (1)

marc_s
marc_s

Reputation: 754518

Since you're not specifying an explicit ORDER BY clause, there is no ordering (this is the default behavior for any RDBMS).

The data is returned in whichever fashion the database engine feels like it. This might look ordered - but there's no guarantee - next time you run the query (without ORDER BY), your results returned could be in a different order.

If you need ordering - you MUST specify an ORDER BY clause!

See this blog post Without ORDER BY, there is no default sort order by Alexander Kuznetsov which contains a nice simple repro script that demonstrates this fact - no ORDER BY == no default ordering ....

Upvotes: 3

Related Questions