Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Distinct Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 7:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 1,918, Visits: 1,439
Thanks for the question. Much easier for my brain to handle in lieu of yesterday's this early in the morning. Hugo, thanks for taking the time to provide even more detail on the explanation.



Everything is awesome!
Post #1450116
Posted Tuesday, May 7, 2013 7:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Thanks Hugo

Still nobody forget yesterday QOD experience.




Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1450121
Posted Tuesday, May 7, 2013 8:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 1,001, Visits: 2,282
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 needed

WRONG!!!!!!
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.
Post #1450156
Posted Tuesday, May 7, 2013 8:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 1,825, Visits: 1,287
Very good question, thank you Mr. Ocean!

I didn't know about this behaviour, but giving it some thought before answering made me realize the right answer.

Also, thank you Hugo for the further explanation (as usual). I had figured out the impossibility of ordering by a column not included in the select clause with distinct, but I didn't know the server included the ordered columns in the select list (logically, at least). I also didn't know ANSI SQL required ordered columns to be in the select list.
Post #1450189
Posted Tuesday, May 7, 2013 10:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:20 AM
Points: 1,339, Visits: 1,715
SQLRNNR (5/6/2013)
What, no controversy for this question?

Thanks Vinay


Funny you should say that. I answered with a small degree of uncertainty, and read the discussion with greater trepidation, because I was convinced someone would see the all-caps column names in the ORDER BY clause and raise the whole collation debate.

I'm glad to see that that has not happened.
Post #1450228
Posted Tuesday, May 7, 2013 10:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 1,001, Visits: 2,282
sknox (5/7/2013)
...

I'm glad to see that that has not happened.


+1

(now people are getting smart and they are considering CI_AS as the default collation when not mentioned by author )


ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1450231
Posted Tuesday, May 7, 2013 10:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,939, Visits: 1,162
Simple and an Easy one

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1450253
Posted Wednesday, May 8, 2013 12:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:10 AM
Points: 854, Visits: 163
Nice one....
Post #1450424
Posted Wednesday, May 8, 2013 7:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:11 AM
Points: 270, Visits: 236
Lokesh Vij (5/6/2013)
Thanks for the easy one Vinay, after tricky Qotd yesterday


+1 nice question....


Manik
You cannot get to the top by sitting on your bottom.
Post #1450537
Posted Wednesday, May 8, 2013 10:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 10:48 AM
Points: 4,126, Visits: 3,427
manik123 (5/8/2013)
Lokesh Vij (5/6/2013)
Thanks for the easy one Vinay, after tricky Qotd yesterday


+1 nice question....

+ another 1, with thanks
Post #1450690
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse