Distinct

Question

Distinct

Vinay Kumar

SSCertifiable

Points: 6099
More actions
May 6, 2013 at 8:42 pm

#272234

Comments posted to this topic are about the item Distinct
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
May 7, 2013 at 3:01 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

Thanks Vinay for the question... 🙂

I was not aware of the thing that if we use DISTINCT then Column names specified in the ORDER BY clause, must be defined in the select list.

Learned some basic thing 😛

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

Thanks for the easy one Vinay, after tricky Qotd yesterday 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 3

Lokesh Vij (5/6/2013)
Thanks for the easy one Vinay, after tricky Qotd yesterday 🙂

🙂

Qotd yesterday :w00t::w00t::w00t:

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 4

kapil_kk (5/6/2013)
Thanks Vinay for the question... 🙂
I was not aware of the thing that if we use DISTINCT then Column names specified in the ORDER BY clause, must be defined in the select list.
Learned some basic thing 😛

Thank Kapil. This question solved its purpose. 🙂

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

What, no controversy for this question?

Thanks Vinay

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 6

Classic;

Thank you for the post, so simple and yet it keeps us busy getting it correct. 🙂

There is one SQL batch (a report data) where I used DISTINCT on all for the final sql statement, just to make sure that no data is getting repeated (even though the batch provides the unique records); and I was wondering rather than using DISTINCT, better to use GROUP BY (with no calculation, just to make the records unique), later when I tested the actual execution plan I came to know the even GROUP BY makes a logical distinct sort... then I again changed it back to DISTINCT.

I guess, when we DISTINCT, it also sorts the data physically, so usage of additional ORDER BY is not needed, like depending on the column list we mention in the SELECT.

SELECT DISTINCT City, Country FROM #Table

SELECT DISTINCT Country, City FROM #Table

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 7

Good question, and after yesterdays made me re read it to see how it was run

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

Thanks! Good question.

In addition to the explanation given: the restriction actually makes sense. Remember that, logically, the ORDER BY is performed *after* the SELECT. In the official ANSI standard, columns in the ORDER BY must always be in the SELECT clause. The fact that we usually can order by other columns is because, logically, SQL Server adds a hidden extra column in the SELECT, uses it for ordering, then doesn't disply it or return it to the client. (And I stress that this is "logically" - queries are not actually executed that way!). But with a SELECT DISTINCT, that is impossible. Adding the extra column before the DISTINCT would influence the results (the extra columns could make rows that are otherwise the same distinct to each other, and once removed the results would show duplicate rows in spite of the DISTINCT). And adding the extra column after the DISTINCT is impossible, since a single row after DISTINCT can correspond to multiple rows before DISTINCT.

Or, yet another way to put it, suppose I have this data in a table called MyTable:

Col1 | Col2

-----+-----

1 | a

2 | b

3 | a

Now suppose I were allowed to execute this query:

SELECT DISTINCT Col2

FROM MyTable

ORDER BY Col1;

The resultset should contain an "a" and a "b". But in what order? The "b" comes from the row with Col1 = 2, but the "a' comes from two rows, with Col1 = 1 and Col1 = 3. Should the "a" go before or after the "b"? There is no possible answer for this - and that's why the query is illegal!

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.

The optimizer had different ways to implement a DISTINCT. One of them is a "Distinct sort" - where rows are sorted and duplicate are removed. That would produce results in the specified output - though the optimizer could decide to reverse the order of the columns. Another way would be to convert the DISTINCT to an aggregate - remember that there is no difference at all between "SELECT DISTINCT Col1 FROM MyTable" and "SELECT Col1 FROM MyTable GROUP BY MyCol", and the optimizer knows that. If the aggregeate is implemented with a stream aggregate operator. you may still be okay (though, again, the order of the columns if more than one is used can be changed to match an existing index and avoid a sort step). But if a hash aggregate operator is used, you're completely hosed.

And then, with a large enough table, you can get a parallel plan, where each individual stream might or might not have the rows in order, but the order is not retained when gathering streams. Or, with a complex query, the optimizer might decide to push down the distinct operator as far as possible to reduce the rows, and then reorder the results coming out of it for the rest, e.g. to facilitate a merge join, or as a byproduct of a hash join.

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!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Dana Medley SSCertifiable Points: 6764 More actions · Answer 9

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!

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 10

Thanks Hugo 🙂

Still nobody forget yesterday QOD experience. :w00t:

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 11

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 SSCrazy Points: 2643 More actions · Answer 12

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 SSChampion Points: 12388 More actions · Answer 13

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.