CODEWITHSUNDEEP
sqlsql-servert-sql
Alex Finelt
Alex Finelt

Reputation: 113

Why does this SQL query returns data in descending order?

Trying to understand why this query produces results in descending order

SELECT
    DateDiff(minute, convert(datetime, SUBSTRING(MSH_07_DateTime, 1, 8) + ' ' + SUBSTRING(MSH_07_DateTime, 9, 2) + ':' + SUBSTRING(MSH_07_DateTime, 11, 2), 100), GetDate()) 
FROM 
    EPDBHL7.DBO.[HL7MSH] 
WHERE
    MessageStatus <> 'ok'

Upvotes: 0

Views: 81

Answers (4)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

Unless you use an ORDER BY you cant guarantee any result order. So you probably get the order based in how the data is stored or how the planner get the data using the index

just add

 where MessageStatus <> 'ok' 
 order by DateDiff(minute,convert(datetime, SUBSTRING ( MSH_07_DateTime ,1 , 8 ) + ' '+ SUBSTRING ( MSH_07_DateTime ,9 , 2 ) + ':'+ SUBSTRING ( MSH_07_DateTime ,11 , 2 ), 100) 
 or 
 order by 1

to order using first column

Upvotes: 2

Doruk
Doruk

Reputation: 912

Unless you use an ORDER BY, you get the result in the way it is stored physically. It may be in the exact correct order but not really, programatically, ordered.

Upvotes: 0

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

Select DateDiff(minute,convert(datetime, SUBSTRING ( MSH_07_DateTime ,1 , 8 ) + ' '+ SUBSTRING ( MSH_07_DateTime ,9 , 2 ) + ':'+ SUBSTRING ( MSH_07_DateTime ,11 , 2 ), 100) as date_diff,GetDate()) 
FROM EPDBHL7.DBO.[HL7MSH] 
where MessageStatus <> 'ok' 
order by date_diff DESC

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

If you are not specifying the ORDER BY clause then the order in which you are going to get the result is unspecified.

If you want to rely on the order of a query, ALWAYS add ORDER BY

Upvotes: 0

Related Questions