SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Distinct


Distinct

Author
Message
Dana Medley
Dana Medley
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3120 Visits: 1706
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!
Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 1549
Thanks Hugo :-)

Still nobody forget yesterday QOD experience. w00t

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
Raghavendra Mudugal
Raghavendra Mudugal
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3350 Visits: 2958
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.
Primo Dang
Primo Dang
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2104 Visits: 1396
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.
sknox
sknox
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3996 Visits: 2924
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.
Raghavendra Mudugal
Raghavendra Mudugal
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3350 Visits: 2958
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 :-P)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2729 Visits: 1189
Simple and an Easy one

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Bangla
Bangla
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1877 Visits: 180
Nice one....
manik_anu
manik_anu
SSChasing Mays
SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)

Group: General Forum Members
Points: 637 Visits: 313
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.
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12417 Visits: 5010
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search