Distinct

  • Comments posted to this topic are about the item Distinct

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

  • 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/

  • 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

  • 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 !!!

  • 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 !!!

  • 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

  • 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.

  • 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

  • This was removed by the editor as SPAM

  • 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/

  • 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!

  • Thanks Hugo 🙂

    Still nobody forget yesterday QOD experience. :w00t:

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

  • 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.

  • 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.

  • 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.

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

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