Hugo Kornelis (5/7/2013)
Raghavendra Mudugal (5/7/2013)
I guess, when we DISTINCT, it also sorts the data physically, so usage of additional ORDER BY is not neededWRONG!!!!!!
This may or may not work correctly, but you have no guarantee.
...
When working with SQL Server, if you need guarantees about the order in which results will be returned - ALWAYS USE AN ORDER BY!!!!!
Everything else means you rely on undocumented, and hence unguaranteed behaviour. A ticking timebomb!
Thank you, Hugo for the explanation. I have implemented so many queries in the PROD with use of DISTINCT and no ORDER BY and the order is always the there.... I use ORDER BY only in some cases where the column name is not needed in the SELECT list ... like the LAST_UPDATE or CREATE_DATE and I use the needed select column and use ORDER BY with the date columns in DESC and then sort it.
Yes, true as there is no guarantee then better not to stick to the code which may or may not work.
Thank you, again, Hugo.
(now I am curious when my code will break and order is not seen and need real time scenario so I can make a proposal for script code change and re-deployment.)
-///edti 1; added the scenario part in the last bracketed line.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.